Skip to content
Advertisement

Query to show top 3 records per user where the user has submitted a minimum of 3?

I have in a table in MS SQL with multiple entries per user. I am trying to get the top 3 entries by date for each user. I have a query that returns returns the maximum top 3 entries per user but is also returning users which have submitted 2 or 1 entries. I have a join with another table only to get the email address. I would like it to return only the entries by john and dave as they have 3 entries. If they have more than 3 just return the top 3 by submitmonth.

    select * from (
    select m.Email, q.submitmonth, q.A2, q.A7, q.C7, q.C8, q.C16, q.F9, q.F10, q.G4, q.H1, q.H2, q.J2, q.J13, q.K18, q.N1, q.P6,
           row_number() over (partition by q.userid order by q.submitmonth desc) as Submitted 
    from dbo.submission q
left join dbo.users m
on q.UserId = m.UserId ) ranks
where Submitted  < 4

this returns

| Email             | submitmonth   | A2   | A7   | Submitted
|                   |               |      |      |
| john@yahoo.com    |  01/08/2020   | 2    | 4    |    1
| john@yahoo.com    |  01/07/2020   | 8    | 8    |    2
| john@yahoo.com    |  01/06/2020   | 2    | 1    |    3
| bob@gmail.com     |  01/08/2020   | 1    | 3    |    1
| bob@gmail.com     |  01/07/2020   | 9    | 7    |    2
| pete@yahoo.co.uk  |  01/08/2020   | 8    | 5    |    1
| dave@gmail.com    |  01/06/2020   | 3    | 6    |    1
| dave@gmail.com    |  01/04/2020   | 5    | 6    |    2
| dave@gmail.com    |  01/02/2020   | 1    | 6    |    3

Thanks for your help.

Advertisement

Answer

Add the count window function and then filter on it.

select *
from (
    select m.Email, q.submitmonth, q.A2, q.A7, q.C7, q.C8, q.C16, q.F9, q.F10, q.G4, q.H1, q.H2, q.J2, q.J13, q.K18, q.N1, q.P6
        , row_number() over (partition by q.userid order by q.submitmonth desc) as Submitted
        , count(*) over (partition by q.userid) TotalSubmitted
    from dbo.submission q
    left join dbo.users m on q.UserId = m.UserId
) ranks
where Submitted < 4 and TotalSubmitted >= 3
User contributions licensed under: CC BY-SA
3 People found this is helpful
Advertisement