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

Question

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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  gt  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  lt  w date WHERE w username IS NULL

User · Answer

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

User · Answer

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

User · Answer

SELECT   FROM ReportStatus c inner join   SELECT    MAX Date  AS MaxDate   FROM ReportStatus   m on  c date   m maxdate

User · Answer

You would use aggregate function MAX and GROUP BY  SELECT username  MAX date   value FROM tablename GROUP BY username  value

User · Answer

SELECT        FROM MyTable T1     WHERE date        SELECT max date     FROM MyTable T2    WHERE T1 username T2 username

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

SELECT MAX DATE  AS dates  FROM assignment   JOIN paper submission detail ON  assignment PAPER SUB ID         paper submission detail PAPER SUB ID

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

SELECT   FROM TABEL1 WHERE DATE   SELECT MAX CREATED DATE  FROM TABEL1

User · Answer

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

User · Answer

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  lt  b date WHERE b username IS NULL ORDER BY a date desc

User · Answer

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

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

Examples related to sql

Examples related to greatest-n-per-group