CODEWITHSUNDEEP
mysqlsql
p9807
p9807

Reputation: 615

Simple MYSQL distinct select

If I have a table with two columns, name and timestamp, and a bunch of rows that will have shared names. How do I select the most recent row of each set of rows that shares the same name?

Thanks!

Upvotes: 0

Views: 130

Answers (2)

Aziz Shaikh
Aziz Shaikh

Reputation: 16524

SELECT name, MAX(timestamp) FROM Table1 GROUP BY name

EDIT: Based on the comment, please try the following:

SELECT name, timestamp, col3, col4
FROM   Table1 t1
WHERE  timestamp = (SELECT MAX(t2.timestamp)
              FROM Table1 t2
              WHERE t1.name = t2.name);

Added by Mchl

Version with no dependent subquery (should perform better)

SELECT 
  t1.name, t1.timestamp, t1.col3, t1.col4
FROM   
  Table1 AS t1
CROSS JOIN (
  SELECT 
    name, MAX(timestamp) AS timestamp
  FROM 
    Table1
  GROUP BY
     name
) AS sq
USING (name,timestamp)

Upvotes: 2

Inca
Inca

Reputation: 1901

Then you need a subquery:

SELECT columns 
FROM Table1 t1
WHERE row_id = (SELECT row_id 
                FROM table1 t2
                WHERE t1.name = t2.name
                ORDER BY timestamp DESC 
                LIMIT 1)
GROUP BY name

Edited, forgot the group by name

Upvotes: 1

Related Questions