snizkorod snizkorod -4 years ago 74
SQL Question

SQL Server 2008 SELECT top 3 based on GROUP result of distinct values

I have a couple tables:

Table A.

id | Extra Columns | report ID
---+---------------+----------
1 | datetime | 1
2 | datetime | 3
3 | datetime | 4
4 | datetime | 1
5 | datetime | 1


Table B.

Report id | customer ID|
----------+------------+
1 | Joe |
2 | Joe |
3 | Joe |
4 | Joe |
1 | Sally |
2 | Sally |
3 | Sally |


What i'd like to do is select N entries (if they exist) from Table A For each Report belonging to Customer "Joe", which is cross-referenced in Table B. The output should look something like this:

id | datetime | report ID
---+----------+----------
1 | datetime | 1
4 | datetime | 1
5 | datetime | 1
2 | datetime | 3
3 | datetime | 4


Of Note, there are many other support tables that i will be Joining to pull data but these two are are giving me a headache. Table A is enormous but runs an index over the ID.

Answer Source

You can use ROW_NUMBER() here to number the matching reports for the customer Joe, and then retain the first N reports. In the query below, I retain the first 3 though you can change this value.

SELECT t.id, t.[Extra Columns], t.[report ID]
FROM
(
    SELECT a.id, a.[Extra Columns], a.[report ID],
           ROW_NUMBER() OVER (PARTITION BY b.[customer ID] ORDER BY b.[Report ID]) rn
    FROM tableB b
    INNER JOIN tableA a
        ON b.[Report id] = a.[report ID]
    WHERE b.[customer ID] = 'Joe'
) t
WHERE t.rn <= 3
ORDER BY t.[report ID]
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download