CODEWITHSUNDEEP
mysqldatabasegroup-by
Adam Esterle
Adam Esterle

Reputation: 373

Select row with largest timestamp in each category

I have a MySQL database (blogs) that has a id, title, timestamp, and category_id column names. How can I select one row from each category that has the newest timestamp? If I have 3 rows being in category 1, 2, 1, then I will get two rows back with the highest timestamp of the categories in "1" as the first and the row with category "2" as the second.

I tried:

SELECT title, timestamp, category_id
FROM blogs 
WHERE timestamp IN (
  SELECT MAX(timestamp) 
  FROM blogs 
  GROUP BY category_id
)

BUT since timestamp is not unique, it could, say, pull in an extra row with category_id = 1 that has the same timestamp as the row with category_id = 2 that was selected in the inner select statement.

Upvotes: 9

Views: 10224

Answers (2)

FullStackEngineer
FullStackEngineer

Reputation: 311

You can try this way too. Works for me when I tried in MYSQL 5.6. It returns two categories with the highest timestamp and ascending order of the category_id.

SELECT title , timestamp , category_id FROM blogs WHERE timestamp in (select max(timestamp) from blogs group by category_id) group by timestamp,category_id order by category_id asc;

Upvotes: 0

spencer7593
spencer7593

Reputation: 108460

For MySQL, this query will return the result set you specified:

SELECT b.title
     , MAX(b.timestamp)
     , b.category_id
  FROM blogs b
 GROUP BY b.category_id

If there happen to be more than one "title" in a category that has the same latest "timestamp", only one row for that category will be returned, so you will get just one "title" returned for each category.

Note: other DBMS system will throw an exception (error) with a query like the one above, because of the handling of non-aggregates in the SELECT list that don't appear in the GROUP BY.

Your query was very close. You've already got that inner query returning the "latest" timestamp for each category. The next step is to return the category_id along with that latest timestamp.

SELECT category_id, MAX(timestamp) AS timestamp
  FROM blogs
 GROUP BY category_id

The next step is to join that back to blogs, to get the associated "title"(s) 

SELECT b.title
     , b.timestamp
     , b.category_id
  FROM (SELECT category_id, MAX(timestamp) AS timestamp
          FROM blogs 
         GROUP BY category_id
       ) l
  JOIN blogs b
    ON b.category_id = l.category_id AND b.timestamp = l.timestamp

NOTE: if there is more than one "latest" row for a category (the timestamp values match), this query will return both of them.

If that's a concern, the query can be modified (or written in a different way) to prevent any possibility of two rows for a category.

Simply adding a GROUP BY clause to that query will work (in MySQL only, not other DBMSs)

SELECT b.title
     , b.timestamp
     , b.category_id
  FROM (SELECT category_id, MAX(timestamp) AS timestamp
          FROM blogs 
         GROUP BY category_id
       ) l
  JOIN blogs b
    ON b.category_id = l.category_id AND b.timestamp = l.timestamp
 GROUP BY b.timestamp, b.category_id

(For other DBMSs, you could modify the SELECT list, replace b.title with MAX(b.title) AS title. That will work when you are returning a single column from the row.

If you want the rows returned in a particular order, add an ORDER BY clause.

Upvotes: 10

Related Questions