greatest n per group - how do I query sql for a latest record date for each user

ID : 10044

viewed : 31

Tags : sqlgreatest-n-per-groupsql

Top 5 Answer for greatest n per group - how do I query sql for a latest record date for each user

vote vote

97

select t.username, t.date, t.value from MyTable t inner join (     select username, max(date) as MaxDate     from MyTable     group by username ) tm on t.username = tm.username and t.date = tm.MaxDate 
vote vote

90

Using window functions (works in Oracle, Postgres 8.4, SQL Server 2005, DB2, Sybase, Firebird 3.0, MariaDB 10.3)

select * from (     select         username,         date,         value,         row_number() over(partition by username order by date desc) as rn     from         yourtable ) t where t.rn = 1 
vote vote

75

I see most of the developers use an inline query without considering its impact on huge data.

Simply, you can achieve this by:

SELECT a.username, a.date, a.value FROM myTable a LEFT OUTER JOIN myTable b ON a.username = b.username  AND a.date < b.date WHERE b.username IS NULL ORDER BY a.date desc; 
vote vote

62

To get the whole row containing the max date for the user:

select username, date, value from tablename where (username, date) in (     select username, max(date) as date     from tablename     group by username ) 
vote vote

57

From my experience the fastest way is to take each row for which there is no newer row in the table.

Another advantage is that the syntax used is very simple, and that the meaning of the query is rather easy to grasp (take all rows such that no newer row exists for the username being considered).

NOT EXISTS

SELECT username, value FROM t WHERE NOT EXISTS (   SELECT *   FROM t AS witness   WHERE witness.username = t.username AND witness.date > t.date ); 

ROW_NUMBER

SELECT username, value FROM (   SELECT username, value, row_number() OVER (PARTITION BY username ORDER BY date DESC) AS rn   FROM t ) t2 WHERE rn = 1 

INNER JOIN

SELECT t.username, t.value FROM t INNER JOIN (   SELECT username, MAX(date) AS date   FROM t   GROUP BY username ) tm ON t.username = tm.username AND t.date = tm.date; 

LEFT OUTER JOIN

SELECT username, value FROM t LEFT OUTER JOIN t AS w ON t.username = w.username AND t.date < w.date WHERE w.username IS NULL 

Top 3 video Explaining greatest n per group - how do I query sql for a latest record date for each user

Related QUESTION?