MySQL: Get most recent record

Question

In the table below, how do I get just the most recent record of id=1 based on the signin column and not all 3 records?

+----+---------------------+---------+
| 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    |
+----+---------------------+---------+

Answer 1

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

Answer 2

SELECT * FROM (SELECT * FROM tb1 ORDER BY signin DESC) GROUP BY id;

Answer 3

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

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
• PostgreSQL sort by datetime asc, null first?

Answer 4

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!

Answer 5

Select [insert your fields here]
from tablename 
where signin = (select max(signin) from tablename where ID = 1)

Answer 6

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