SQL Server - Display the first 12 records for each user

Question

I need to display the 12 first result for each user, I tried Top(12) and it only select the first 12 rows, then I did some search and found out i need to use aggregation functions, this is what I have:

SELECT id, fname, lnam, Invoice, amnt, [bill period] 
FROM TABLE1
GROUP BY id, fname, lnam, Invoice, amnt, [bill period] 
HAVING --Not really sure!

This is Table1

id  fname   lname   Invoice amnt    bill period
1   John    Doe 480991  38.42   201406
1   John    Doe 481102  38.42   201407
1   John    Doe 481047  38.42   201408
1   John    Doe 485053  38.42   201409
1   John    Doe 489759  38.42   201410
1   John    Doe 489788  38.42   201411
1   John    Doe 489817  38.42   201412
1   John    Doe 489846  38.42   201501
1   John    Doe 489875  38.42   201502
1   John    Doe 489905  38.42   201503
1   John    Doe 489933  38.42   201504
1   John    Doe 489963  38.42   201505
1   John    Doe 490044  38.42   201506
1   John    Doe 490138  38.42   201507
2   Rich    Doe 480992  41.41   201406
2   Rich    Doe 481103  41.41   201407
2   Rich    Doe 481048  41.41   201408
2   Rich    Doe 485057  41.41   201409
2   Rich    Doe 489765  41.41   201410
2   Rich    Doe 489794  41.41   201411
2   Rich    Doe 489823  41.41   201412
2   Rich    Doe 489852  41.41   201501
2   Rich    Doe 489881  41.41   201502
2   Rich    Doe 489911  41.41   201503
2   Rich    Doe 489936  41.41   201504
2   Rich    Doe 489979  41.41   201505
2   Rich    Doe 490066  41.41   201506
2   Rich    Doe 490160  41.41   201507
2   Rich    Doe 490161  41.41   201508

Thank you.

Ranking with Partition and then filter should work in this case. — user2989408, Jun 06 '14 at 17:56
I think this is an old question. You can find it here :) http://stackoverflow.com/questions/176964/select-top-10-records-for-each-category — CHT, Jun 06 '14 at 17:57

VJ Hil · Accepted Answer · 2014-06-06T18:22:13.087

2

---- Use the analytic function rank() so that it returns the rank of a value in a group of values.With clause will hep you create a temporary set of data.

WITH TEMP AS
(
SELECT id, fname, lnam, Invoice, amnt, bill_period,
rank() OVER (PARTITION BY ID ORDER BY bill_period) AS RK
FROM TABLE1
)
SELECT id, fname, lnam, Invoice, amnt, bill_period FROM TEMP WHERE RK<13;

edited Jun 06 '14 at 18:22

answered Jun 06 '14 at 17:57

VJ Hil

904
6
15

score 1 · Answer 2 · answered Jun 06 '14 at 17:57

Instead of using a group by clause, you can use the analytic row_number() function. Assuming you identify a user according to the id column:

SELECT id, fname, lnam, Invoice, amnt, [bill period]
FROM   (SELECT id, fname, lnam, Invoice, amnt, [bill period], 
        ROW_NUMBER() OVER (PARTITION BY id ORDER BY [bill period] ASC) AS rn 
        FROM TABLE1) t
WHERE  rn <= 12

score 0 · Answer 3 · answered Jun 06 '14 at 18:07

0

SELECT t2.*
FROM (SELECT DISTINCT id FROM TABLE1) t1
CROSS APPLY (SELECT TOP 12 * FROM t1 WHERE id = t1.id ORDER BY bill_period) t2

answered Jun 06 '14 at 18:07

Anon

10,660
1
29
31

SQL Server - Display the first 12 records for each user

3 Answers3