Two most recent records per user after an already complicated SQL statement (for me)

Question

I'm totally new with SQL. I have two tables that I connect with an inner join, sorted, and filtered. Then I need to show only the two most recent entries grouped by "username." I don't know how to do that. I've looked at some of the solutions posted, but I don't know how to apply those to my situation.

Here's where I am now:

SELECT users.username, logs.event, logs.insertdate
FROM users 
INNER JOIN logs ON users.identifier = logs.useridentifier
WHERE (((logs.event)="Taken" Or (logs.event)="Return"))
ORDER BY users.username, logs.insertdate DESC;

Here's an example of the output I get:

|username | event | insertdate | 
================================
 alan     | Return| 10/15/2014 3:17 PM
 alan     | Taken | 10/15/2014 6:49 AM
 alan     | Return| 10/14/2014 3:16 PM
 alan     | Taken | 10/14/2014 6:50 AM
 alan     | Return| 10/13/2014 3:15 PM
 bill     | Return| 10/15/2014 3:19 PM
 bill     | Taken | 10/15/2014 6:53 AM
 bill     | Return| 10/14/2014 3:26 PM
 bill     | Taken | 10/14/2014 7:00 AM
 bill     | Return| 10/13/2014 3:19 PM

and so on. What I'm hoping to do is get just the top two entries from each user. So

|username | event | insertdate | 
================================
 alan     | Return| 10/15/2014 3:17 PM
 alan     | Taken | 10/15/2014 6:49 AM
 bill     | Return| 10/15/2014 3:19 PM
 bill     | Taken | 10/15/2014 6:53 AM

Thanks

Which database? Something like this http://stackoverflow.com/a/176985/203968 — oluies, Oct 15 '14 at 22:51
SQL Server 2008. I'll take a look at your link to see if I can make sense of it. — Barry, Oct 15 '14 at 23:01
Window function: `... rank() AS rnk over (partition by username order by insertdate DESC) ... where rnk <=2`... — wildplasser, Oct 15 '14 at 23:03
Voted this one up as it's a really well laid out question for someone new to SO. Wish others would follow suit :-) — Darren Wainwright, Oct 16 '14 at 02:16

Paul Maxwell · Accepted Answer · 2014-10-16T00:29:11.620

use the "analytic" function row_number()

SELECT
      username
    , event
    , insertdate
FROM (
            SELECT
                  users.username
                , logs.event
                , logs.insertdate
                , ROW_NUMBER() OVER (PARTITION BY users.username
                                     ORDER BY logs.insertdate DESC) AS rn
            FROM users
                  INNER JOIN logs
                              ON users.identifier = logs.useridentifier
            WHERE (logs.event = "Taken"
                  OR logs.event = "Return")
      ) AS derived
WHERE rn < 3
ORDER BY
      username, insertdate DESC;

This variant might be worth trying to see if it helps performance (or not):

SELECT
      users.username
    , logrn.event
    , logrn.insertdate
FROM users
      INNER JOIN (
                  SELECT
                        logs.useridentifier
                      , logs.event
                      , logs.insertdate
                      , ROW_NUMBER() OVER (PARTITION BY logs.useridentifier
                                           ORDER BY logs.insertdate DESC) AS rn
                  FROM logs
                  WHERE (logs.event = "Taken" OR logs.event = "Return")
            ) AS logrn
                  ON users.identifier = logrn.useridentifier
                        AND rn < 3
ORDER BY
      users.username, logrn.insertdate DESC
;

I recommend row_number(). rank() or dense_rank() can produce the same number for multiple rows, hence if you want to ensure there is only 2 rows, don't use ranking functions. — Paul Maxwell, Oct 16 '14 at 00:21

Two most recent records per user after an already complicated SQL statement (for me)

1 Answers1