CODEWITHSUNDEEP
mysqlsqlgreatest-n-per-group
enchance
enchance

Reputation: 30411

Get most recent row for given ID

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

Upvotes: 121

Views: 293965

Answers (7)

Erwin Brandstetter
Erwin Brandstetter

Reputation: 656291

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:

The SQL standard does not explicitly define a default sort order for NULL values. The behavior varies quite a bit across different RDBMS. See:

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:

Upvotes: 102

Ajmal Jamil
Ajmal Jamil

Reputation: 829

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!

Upvotes: 1

Ales
Ales

Reputation: 441

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

Upvotes: 1

user3044573
user3044573

Reputation: 11

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

Upvotes: 1

Michael Berkowski
Michael Berkowski

Reputation: 270609

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

Upvotes: 137

rantsh
rantsh

Reputation: 602

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)

Upvotes: 11

xQbert
xQbert

Reputation: 35323

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

Upvotes: 4

Related Questions