Top 5 by user query

Question

Top 5 by user query

schleep

SSChampion

Points: 12585
More actions
April 8, 2021 at 12:39 pm
Go to Answer
#3866825
Hi All,
I want the earliest 5 distinct dates for each user.
I know I can row_number() over partition by user order by date, but there are a lot more than 3 users and a lot more than 12 dates.
I'm having a moment here, and I'm pretty there's a much simpler solution.
This will generate unordered test data.
```
IF OBJECT_ID('tempdb..#tmp_UserDates') IS NOT NULL
DROP TABLE #tmp_UserDates
GO

SELECTcUser,
dDate = CAST(DATEADD(DAY, t.n, DATEADD(MILLISECOND, ABS(CHECKSUM(NEWID())), '1 Jan 2021')) AS DATE)
INTO#tmp_UserDates
FROM(SELECT cUser = 'User1'
UNION SELECT 'User2'
UNION SELECT 'User3') AS Users
CROSS JOIN (VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9), (10), (11), (12)) AS t(n)
ORDER BY NEWID()
GO
```
Thanks!
P
Erland Sommarskog

SSC-Insane

Points: 24038
More actions
April 8, 2021 at 1:11 pm
Answer
#3866840
```
; WITH numbering AS (
    SELECT cUser, dDate, dense_rank() OVER(PARTITION BY cUser ORDER BY dDate) AS drank
    FROM   #tmp_UserDates
)
SELECT DISTINCT cUser, dDate
FROM   numbering
WHERE  drank <= 5
```
An alternative is to use CROSS APPLY with a SELECT TOP query, but
1. With many users, and few dates per users, the above us more efficient.
2. Since you want distinct dates, the CROSS APPLY thing gets a little more complicated.
[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Johan Bijnens SSC Guru Points: 135369 More actions · Answer 1

This may be a start

;With cteSel as (
Select cUser, dDate, rank() over ( partition by cUser Order by cUser, dDate desc) UserDateRank
from #tmp_UserDates
) 
Select distinct *
from cteSel
where UserDateRank < 6
order by cuser, dDate desc

Johan

Learn to play, play to learn !

Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:

- How to post Performance Problems
- How to post data/code to get the best help[/url]

- How to prevent a sore throat after hours of presenting ppt

press F1 for solution, press shift+F1 for urgent solution 😀

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me

schleep SSChampion Points: 12585 More actions · Answer 2

Yup, that does it. I'd distilled Johan's solution down to yours. Perf not completely horrible in real life, and I only need to run this once a month.

Thank you both.