select * from ( select (ROW_NUMBER() OVER (order by (select top 1 SP.price from contemp_Prices SP where SP.productid = P.ID and SP.show=0 order by SP.price))) as row, P.id, P.title, P.thumb, P.image, P.friendlyurl,P.friendlyurlnew, case when P.ID in (select SP.productid from contemp_Prices SP where SP.productid = P.ID and SP.show=0 and SP.price <> SP.RRprice) then 'Yes' when not P.ID in (select SP.productid from contemp_Prices SP where SP.productid = P.ID and SP.show=0 and SP.price <> SP.RRprice) then 'No' end as isSaleItem, zoomimage = (select top 1 SP.zoomimage from contemp_Prices SP where SP.productid = P.ID and SP.show=0 and SP.zoomimage <> '' order by SP.id), manufriendlyurl = (select top 1 M.friendlyurl from manufacturers M where M.id = P.manufacturer), cat2name = (select top 1 C.name from contemp_cat_2 C where C.id = P.design), Price = (select top 1 SP.price from contemp_Prices SP where SP.productid = P.ID and SP.show=0 order by SP.price), RRPrice = (select top 1 SP.RRprice from contemp_Prices SP where SP.productid = P.ID and SP.show=0 order by SP.price), customise = (select top 1 SP.customise from contemp_Prices SP where SP.productid = P.ID and SP.show=0 order by SP.price), priceid = (select top 1 SP.id from contemp_Prices SP where SP.productid = P.ID and SP.show=0 order by SP.price) from contemp_products P where P.show=0 and P.logicaldelete=0 and P.cat <> 22 and P.cat <> 30 and P.cat in (select top 1 PT.id from contemp_Cat_1 PT where P.cat = PT.id and PT.show=0) and P.ID in (select top 1 SP.productid from contemp_Prices SP where SP.productid = P.ID and SP.show=0 order by SP.price) and (P.design=@cat_2 or P.subcats like '%,'+ @cat_2 +',%' or P.subcats like @cat_2 +',%') ) as mydata where row between 25 and 48