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

Related: [Select row with most recent date per user](https://stackoverflow.com/questions/17038193/select-row-with-most-recent-date-per-user/17038667) — Stevoisiak, Dec 02 '21 at 19:23

Michael Berkowski · Accepted Answer · 2013-03-13T16:40:26.303

132

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

edited Mar 13 '13 at 16:40

answered Dec 15 '11 at 16:32

Michael Berkowski

267,341
46
444
390

Here's what worked based on your **first** answer: `SELECT id, MAX(signin) FROM tbl GROUP BY id WHERE id=1;` – enchance Dec 15 '11 at 17:18
I tried to implement the 2nd solution (to get the whole row) on my own situation, but I always get an empty result set – rantsh Mar 13 '13 at 16:34
@rantsh xQbert's answer had the correct version of what I originally had above, but I prefer the solution I just changed it ot. – Michael Berkowski Mar 13 '13 at 16:41
@MichaelBerkowski you meant you prefer my solution? I wouldn't mind getting an upvote on my answer if that's the case – rantsh Mar 13 '13 at 17:28
1

@rantsh Ah, I hadn't noticed you had added a similar thing to what I just modified, except via `USING` instead of an explicit `ON`. You got a +1 :) – Michael Berkowski Mar 13 '13 at 17:30
the best way to find MAX value – jonathan gutierrez villamarin Apr 09 '20 at 01:59
I notice the output would be difference if I use LEFT JOIN. What might be the reason? – Oliver Bird Mar 28 '22 at 16:19
@OliverBird If you get different results from a left join I couldn't say without seeing the table data. – Michael Berkowski Mar 28 '22 at 23:48
it's hard for me to post it, at least I noticed it and would be mindful each time as I applied this. TY! – Oliver Bird Mar 30 '22 at 20:04

Erwin Brandstetter · Answer 2 · 2021-02-27T14:40:42.363

96

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.

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:

edited Feb 27 '21 at 14:40

answered Dec 15 '11 at 16:32

Erwin Brandstetter

605,456
145
1,078
1,228

7

This feels like the cleanest way of doing it ! – karancan May 10 '13 at 19:56
Consider creating index for signin column, it will speed up the process significantly – Alexei Tenitski Jan 28 '14 at 22:06
Use `SELECT TOP 1 *` and remove the `LIMIT 1` last line. if using SQL Server. Use the above if MySQL or Postgres. – jaredbaszler Aug 10 '17 at 17:59
1

Cleanest . . . . . . . . – stigzler Jan 05 '19 at 18:30
1

Simple and clean! – goWithSwagger Mar 07 '19 at 14:53
I think this should be the accepted answer. – WinEunuuchs2Unix Feb 27 '21 at 14:31
How do you get the results for each id? Can group by be used in this? – Edward Jan 07 '23 at 01:25
@Edward: There are various ways, depending on exact requirements. This works on MySQL: https://stackoverflow.com/a/3800572/939860 `DISTINCT ON` is one way in Postgres. See: https://stackoverflow.com/a/7630564/939860 And here's how to translate that to MySQL: https://stackoverflow.com/q/17673457/939860 – Erwin Brandstetter Jan 07 '23 at 04:19

score 10 · Answer 3 · answered Mar 13 '13 at 16:33

10

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)

answered Mar 13 '13 at 16:33

rantsh

608
10
30

1

Hi how does this query work and is it more efficient than xQbert's answer? Both queries run at about the same time for me and I don't understand the using part of the query replacing what is usually and on. – ZJS Dec 10 '13 at 18:54

xQbert · Answer 4 · 2011-12-21T23:20:43.473

4

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

edited Dec 21 '11 at 23:20

answered Dec 15 '11 at 16:32

xQbert

34,733
2
41
62

score 1 · Answer 5 · edited Nov 17 '14 at 15:46

1

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

edited Nov 17 '14 at 15:46

rink.attendant.6

44,500
61
101
156

answered Nov 17 '14 at 15:40

user3044573

11
3

score 1 · Answer 6 · answered Feb 20 '18 at 11:46

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

score 1 · Answer 7 · answered Sep 04 '19 at 10:48

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!

Get most recent row for given ID

7 Answers7

Linked

Related