Top 5 by user query

  • 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

    SELECT cUser,
    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

     

  • 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

  • ; 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]

  • 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.

     

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

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