CODEWITHSUNDEEP
mysql
topherg
topherg

Reputation: 4293

Only Return Highest Value for another unique Column

Preferably without downloading the entire database and scanning each item, is there a MySQL query that could access this table:

__________________________________
|   ColID   |      Timestamp      |
|___________|_____________________|
|_____2_____|_2012-08-01_12:00:00_|
|_____1_____|_2012-08-01_12:01:00_|
|_____3_____|_2012-08-01_12:02:00_|
|_____3_____|_2012-08-01_12:03:00_|
|_____2_____|_2012-08-01_12:04:00_|
|_____3_____|_2012-08-01_12:05:00_|

And return only these rows:

__________________________________
|   ColID   |      Timestamp      |
|___________|_____________________|
|_____1_____|_2012-08-01_12:01:00_|
|_____2_____|_2012-08-01_12:04:00_|
|_____3_____|_2012-08-01_12:05:00_|

So as to extract only one of each ColID with the highest Timestamp

Upvotes: 0

Views: 83

Answers (2)

Zane Bien
Zane Bien

Reputation: 23125

Use GROUP BY with MAX:

SELECT   ColID, MAX(Timestamp) AS Timestamp
FROM     tbl
GROUP BY ColID

Also, just a tip you may want to keep in mind for the future: if you wanted to also select other columns that might be in the same table for each maximum ColID, you cannot select it directly in the above query due to the nature of GROUP BY. You will need to wrap the query in a joined subselect joining on both the id and date columns:

SELECT b.*
FROM   tbl a
JOIN   (
       SELECT   ColID, MAX(Timestamp) AS max_timestamp
       FROM     tbl
       GROUP BY ColID
       ) b ON a.ColID = b.ColID AND a.max_timestamp = b.Timestamp

Upvotes: 1

Omesh
Omesh

Reputation: 29081

yes you can achive this by using GROUP BY with MAX as:

SELECT ColID, MAX(Timestamp) AS max_Timestamp      
FROM my_table
GROUP BY ColID;

Upvotes: 1

Related Questions