How to implement?

Hi all,

I had many groups of data which column A store number & column B store location. My syntax as below :-

select count(columnA) as no, columnB from table group by columnB

No columnB

20 A

10 B

5 C

How can I select 5 rows for Each ColumnB? (total 15 rows of record)

Thanks!

check out the docs about using TOP.|||TOP is part of it, but it can't get you what you're looking for. I've done it with a cursor before. Create a temp table or table variable. Create a cursor that is each unique value of B. Loop through the cursor inserting into the temp table the top 5 for each B. When the cursor is done, select from your temp table.|||I think that a temp table and a cursor are probably overkill in thissituation. I'd bet that a corrolated subquery would be moreefficient.
Something like this:

SELECT t1.columnA, t1.columnB
FROM table t1
WHERE t1.columnA IN (SELECT TOP 5 t2.columnA FROM table t2 WHERE t2.columnB = t1.columnB)