2

I find myself needing to get the most recent N things accessed by each user, and I have a log table of all accesses. Something like

CREATE TABLE [AccessLog] ([UserId] UNIQUEIDENTIFIER, [ThingId] UNIQUEIDENTIFIER, [AccessDate] DATETIME)

So I wrote

SELECT [UserId], [ThingId], [SequenceNumber]
  FROM (SELECT [UserId], [ThingId], DENSE_RANK()
          OVER (PARTITION BY [UserId] ORDER BY [AccessDate] DESC) SequenceNumber
          FROM [AccessLog]
     ) [AccessLog]
 WHERE [SequenceNumber] < @N

This doesn't work because if the same user accessed the same thing repeatedly we get the same [ThingId] back up to N times rather than N distinct [ThingIds] back for each user.

I'm baffled how to actually do it. The table is so large that if I have to write the N cubed join like it's 2000 again (join table back on itself once to exclude duplicate copies and a second time to generate the count) it will be faster to suck in the entire table and process it in application code.

It's almost like I want

FOR EACH [UserId] IN (SELECT [UserId] IN [User])
    SELECT TOP (@N) [UserId], [ThingId]
      FROM [AccessLog]
     ORDER BY [AccessDate] DESC

But that performance is also so bad I'd to better reading the table into application code again.

2
  • To be clear, if @N = 3 you want the top 3 unique ThingIds per UserId? If so, when there are dupe ThingIds in that top 3, which row of the dupes should be returned?...the first, the last, or any random row of the dupes?
    – J.D.
    Commented Jun 8, 2023 at 2:15
  • You are correct for @N = 3 I want the top 3 thingids per userid. In fact N is constant; and the select clause makes it not matter which of the rows it got.
    – Joshua
    Commented Jun 8, 2023 at 2:22

1 Answer 1

4

You are correct for @N = 3 I want the top 3 thingids per userid. In fact N is constant; and the select clause makes it not matter which of the rows it got.

Cool, so here's the game plan. Let's group up the AccessLog table by UserId and ThingId so that there's only 1 unique row per, and we'll use the MAX() aggregate function to get the last AccessDate for each, so they can all be relatively compared within your window function.

Here's a query for that:

DECLARE @N INT = 3;

SELECT
    UserId,
    ThingId,
    SequenceRankId
FROM
(
    SELECT
        UserId,
        ThingId,
        DENSE_RANK() OVER
        (
            PARTITION BY UserId
            ORDER BY MAX(AccessDate) DESC -- Ordering by the last access of each Thing per User (MAX() is the key here)
        ) AS SequenceRankId
    FROM AccessLog
    GROUP BY UserId, ThingId -- Ensures only 1 row per ThingId per UserId
) AS AccessLogsRanked
WHERE SequenceRankId <= @N
ORDER BY UserId, SequenceRankId;
1
  • @Joshua Np, best of luck!
    – J.D.
    Commented Jun 8, 2023 at 3:59

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.