Get most recent row for given ID

Question

In the table below  how do I get just the most recent row with id 1 based on the signin column  and not all 3 rows   ---- --------------------- ---------    id   signin                signout    ---- --------------------- ---------     1   2011-12-12 09 27 24   NULL         1   2011-12-13 09 27 31   NULL         1   2011-12-14 09 27 34   NULL         2   2011-12-14 09 28 21   NULL       ---- --------------------- ---------

User · Answer

SELECT   FROM  SELECT   FROM tb1 ORDER BY signin DESC  GROUP BY id

User · Answer

Simple Way To Achieve  I know it s an old question  You can also do something like   SELECT   FROM Table WHERE id 1 ORDER BY signin DESC   In above  query the first record will be the most recent record   For only one record you can use something like   SELECT top 1    FROM Table WHERE id 1 ORDER BY signin DESC   Above query will only return one latest record   Cheers

User · Answer

I had a similar problem  I needed to get the last version of page content translation  in other words - to get that specific record which has highest number in version column  So I select all records ordered by version and then take the first row from result  by using LIMIT clause    SELECT   FROM  page contents translations  ORDER BY version DESC LIMIT 1

User · Answer

Select  insert your fields here  from tablename  where signin    select max signin  from tablename where ID   1

User · Answer

Building on  xQbert s answer s  you can avoid the subquery AND make it generic enough to filter by any ID  SELECT id  signin  signout FROM dTable INNER JOIN    SELECT id  MAX signin  AS signin   FROM dTable   GROUP BY id   AS t1 USING id  signin

User · Answer

SELECT   FROM   tbl WHERE  id   1 ORDER  BY signin DESC LIMIT  1   The obvious index would be on  id   or a multicolumn index on  id  signin DESC   Conveniently for the case  MySQL sorts NULL values last in descending order  That s what you typically want if there can be NULL values  the row with the latest not-null signin  To get NULL values  first  ORDER BY signin IS NOT NULL  signin DESC  You may want to append more expressions to ORDER BY to get a deterministic pick from  potentially  multiple rows with NULL  The same applies without NULL if signin is not defined UNIQUE  Related   mysql order by  null first  and DESC after  The SQL standard does not explicitly define a default sort order for NULL values  The behavior varies quite a bit across different RDBMS  See   https   docs mendix com refguide null-ordering-behavior  But there are the NULLS FIRST   NULLS LAST clauses defined in the SQL standard and supported by most major RDBMS  but not by MySQL  See   SQL how to make null values come last when sorting ascending Sort by column ASC  but NULL values first

User · Answer

Use the aggregate MAX signin  grouped by id  This will list the most recent signin for each id   SELECT   id    MAX signin  AS most recent signin FROM tbl GROUP BY id   To get the whole single record  perform an INNER JOIN against a subquery which returns only the MAX signin  per id   SELECT    tbl id    signin    signout FROM tbl   INNER JOIN       SELECT id  MAX signin  AS maxsign FROM tbl GROUP BY id     ms ON tbl id   ms id AND signin   maxsign WHERE tbl id 1

[mysql] Get most recent row for given ID

Examples related to mysql

Examples related to sql

Examples related to greatest-n-per-group