select top 5 ... from .. where category =1
union all
select top 5 ... from .. where category =2
union all
...

quote:

---

Originally posted by trouble2

Hi I want to build a sp where I get the top 5 records for category 1 to 7, so in total, this should return 35 records.
Let's say the table looks like:

ZOMID Name Clicks Category
1 Bla 300 2
2 BlaBla 221 1
3 BlBl 121 3
5 AaAaA 234 1

So it should return the top 5 of category 1, the top 5 of category 2, etc. based on the clicks.

---

SELECT ZOMID,     Name,      Clicks,       Category
FROM
(
SELECT ROW_NUMBER() OVER (PARTITION BY Category ORDER BY ZOMID) AS Seq,ZOMID,     Name,      Clicks,       Category
FROM Table
)t
WHERE Seq<=5