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