select * from
(
SELECT
ROW_NUMBER() OVER ( order by PMInc desc ) as RNUM, *
FROM
(
SELECT
B.DeleteYes,B.Brand,
L.*
FROM psProdList as L WITH (NOLOCK)
INNER JOIN
psProdMain as B WITH (NOLOCK)
ON
L.PMInc = B.PMInc
WHERE
B.DeleteYes = 0 and B.ViewYes = 1 and B.Stock = 1 and B.Ccode1 != '67' and B.BrandID='STAUD'
) A WHERE 1=1
) AA
WHERE RNUM BETWEEN ((1-1) * 40) + 1 AND 1*40
select BMInc,CustKind,CustID,ServiceID,Brand,BrandID,ProdCnt,ProdCntB,BestYes,ViewYes,MainBanner,DeleteYes,CreateDate8,CreateOper,ModifyDate8,ModifyOper from psBrandMain where BrandID = 'STAUD'
