how do I query sql for a latest record date for each user

I have a table that is a collection entries as to when a user was 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 forgot that I needed to have a value that goes along with the latest date.

23 Answers

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 

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 

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; 

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 

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 ) 

SELECT * FROM MyTable T1 WHERE date = ( SELECT max(date) FROM MyTable T2 WHERE T1.username=T2.username ) 

This one should give you the correct result for your edited question.

The sub-query makes sure to find only rows of the latest date, and the outer GROUP BY will take care of ties. When there are two entries for the same date for the same user, it will return the one with the highest value.

SELECT t.username, t.date, MAX( t.value ) value FROM your_table t JOIN ( SELECT username, MAX( date ) date FROM your_table GROUP BY username ) x ON ( x.username = t.username AND x.date = t.date ) GROUP BY t.username, t.date 

SELECT DISTINCT Username, Dates,value FROM TableName WHERE Dates IN (SELECT MAX(Dates) FROM TableName GROUP BY Username) Username Dates value bob 2010-02-02 1.2 brad 2010-01-02 1.1 fred 2010-01-03 1.0 

This is similar to one of the answers above, but in my opinion it is a lot simpler and tidier. Also, shows a good use for the cross apply statement. For SQL Server 2005 and above...

select a.username, a.date, a.value, from yourtable a cross apply (select max(date) 'maxdate' from yourtable a1 where a.username=a1.username) b where a.date=b.maxdate 

You could also use analytical Rank Function

 with temp as ( select username, date, RANK() over (partition by username order by date desc) as rnk from t ) select username, rnk from t where rnk = 1 

SELECT MAX(DATE) AS dates FROM assignment JOIN paper_submission_detail ON assignment.PAPER_SUB_ID = paper_submission_detail.PAPER_SUB_ID 

If your database syntax supports it, then TOP 1 WITH TIES can be a lifesafer in combination with ROWNUMER.

With the example data you provided, use this query:

SELECT TOP 1 WITH TIES username, date, value FROM user_log_in_attempts ORDER BY ROW_NUMBER() OVER (PARTITION BY username ORDER BY date DESC) 

It yields:

username | date | value ----------------------------- bob | 8/4/2009 | 1.5 brad | 2/2/2010 | 1.2 fred | 12/2/2009 | 1.3 

Demo

How it works:

• ROWNUMBER() OVER (PARTITION BY... ORDER BY...) For each username a list of rows is calculated from the youngest (rownumber=1) to the oldest (rownumber=high)
• ORDER BY ROWNUMBER... sorts the youngest rows of each user to the top, followed by the second-youngest rows of each user, and so on
• TOP 1 WITH TIES Because each user has a youngest row, those youngest rows are equal in the sense of the sorting criteria (all have rownumber=1). All those youngest rows will be returned.

Tested with SQL-Server.

SELECT Username, date, value from MyTable mt inner join (select username, max(date) date from MyTable group by username) sub on sub.username = mt.username and sub.date = mt.date 

Would address the updated problem. It might not work so well on large tables, even with good indexing.

SELECT * FROM ReportStatus c inner join ( SELECT MAX(Date) AS MaxDate FROM ReportStatus ) m on c.date = m.maxdate 

For Oracle sorts the result set in descending order and takes the first record, so you will get the latest record:

select * from mytable where rownum = 1 order by date desc 

SELECT t1.username, t1.date, value FROM MyTable as t1 INNER JOIN (SELECT username, MAX(date) FROM MyTable GROUP BY username) as t2 ON t2.username = t1.username AND t2.date = t1.date 

Select * from table1 where lastest_date=(select Max(latest_date) from table1 where user=yourUserName)

Inner Query will return the latest date for the current user, Outer query will pull all the data according to the inner query result.

I used this way to take the last record for each user that I have on my table. It was a query to get last location for salesman as per recent time detected on PDA devices.

CREATE FUNCTION dbo.UsersLocation() RETURNS TABLE AS RETURN Select GS.UserID, MAX(GS.UTCDateTime) 'LastDate' From USERGPS GS where year(GS.UTCDateTime) = YEAR(GETDATE()) Group By GS.UserID GO select gs.UserID, sl.LastDate, gs.Latitude , gs.Longitude from USERGPS gs inner join USER s on gs.SalesManNo = s.SalesmanNo inner join dbo.UsersLocation() sl on gs.UserID= sl.UserID and gs.UTCDateTime = sl.LastDate order by LastDate desc 

SELECT * FROM TABEL1 WHERE DATE= (SELECT MAX(CREATED_DATE) FROM TABEL1) 

My small compilation

• self join better than nested select
• but group by doesn't give you primary key which is preferable for join
• this key can be given by partition by in conjunction with first_value (docs)

So, here is a query:

 select t.* from Table t inner join ( select distinct first_value(ID) over(partition by GroupColumn order by DateColumn desc) as ID from Table where FilterColumn = 'value' ) j on t.ID = j.ID 

Pros:

• Filter data with where statement using any column
• select any columns from filtered rows

Cons:

• Need MS SQL Server starting with 2012.

I did somewhat for my application as it:

Below is the query:

select distinct i.userId,i.statusCheck, l.userName from internetstatus as i inner join login as l on i.userID=l.userID where nowtime in((select max(nowtime) from InternetStatus group by userID)); 

This should also work in order to get all the latest entries for users.

SELECT username, MAX(date) as Date, value FROM MyTable GROUP BY username, value 

You would use aggregate function MAX and GROUP BY

SELECT username, MAX(date), value FROM tablename GROUP BY username, value