Problem
I have a table that contains entries indicating when a person logged on.
username, date, value
--------------------------
brad, 1/2/2010, 1.1
fred, 1/3/2010, 1.0
bob, 8/4/2009, 1.5
brad, 2/2/2010, 1.2
fred, 12/2/2009, 1.3
etc..
How do I create a query that would give me the latest date for each user?
Update: I neglected to provide a value that corresponded to the most recent date.
Asked by fishhead
Solution #1
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
Answered by D’Arcy Rittich
Solution #2
Making use of 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
Answered by dotjoe
Solution #3
Most developers, in my experience, utilize an inline query without considering the impact on large data sets.
Simply put, you can do so 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;
Answered by sujeet
Solution #4
To retrieve the entire row including the user’s maximum date:
select username, date, value
from tablename where (username, date) in (
select username, max(date) as date
from tablename
group by username
)
Answered by Alison R.
Solution #5
The quickest method, in my experience, is to take each row in the table for which there is no newer row.
Another benefit is that the syntax is quite basic, and the meaning of the query is rather straightforward (take all rows such that no newer row exists for the username being considered).
SELECT username, value
FROM t
WHERE NOT EXISTS (
SELECT *
FROM t AS witness
WHERE witness.username = t.username AND witness.date > t.date
);
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
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;
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
Answered by Fabian Pijcke
Post is based on https://stackoverflow.com/questions/2411559/how-do-i-query-sql-for-a-latest-record-date-for-each-user