MySQL get last result for each where

Question

I have 3 queries:

SELECT `update`.`id` AS `id`, `update`.`type` AS `type`, `update`.`date` AS `date`, `update`.`like` AS `like`, `update`.`dislike` AS `dislike` FROM `updates` AS `update` WHERE `type` = '1' ORDER BY `date` DESC LIMIT 1

SELECT `update`.`id` AS `id`, `update`.`type` AS `type`, `update`.`date` AS `date`, `update`.`like` AS `like`, `update`.`dislike` AS `dislike` FROM `updates` AS `update` WHERE `type` = '2' ORDER BY `date` DESC LIMIT 1 (1)

SELECT `update`.`id` AS `id`, `update`.`type` AS `type`, `update`.`date` AS `date`, `update`.`like` AS `like`, `update`.`dislike` AS `dislike` FROM `updates` AS `update` WHERE `type` = '3' ORDER BY `date` DESC LIMIT 1 (1)

I need to get last date for each type. How can I get it in one query?

Thank you.

score 4 · Accepted Answer · edited Jul 11 '13 at 16:59

4

This must be the #1 most common mysql question.

SELECT u.*
FROM update u
JOIN (SELECT type, MAX(date) maxdate
      FROM update
      WHERE type in ('1', '2', '3')
      GROUP BY type) m
ON u.type = m.type AND u.date = m.maxdate

edited Jul 11 '13 at 16:59

Strawberry

33,750
13
40
57

answered Jul 11 '13 at 16:40

Barmar

741,623
53
500
612

It's possible for this query can return more than three rows. Absent a guarantee that `(type,date)` is unique, this query can return more than one row for a particular type. – spencer7593 Jul 11 '13 at 18:01
@spencer7593 Since the question doesn't specify a criteria for choosing among the rows that might have the same date, I assumed they were unique. `GROUP BY type, date` can be added to the query to remove duplicates, although then the other columns could be randomly selected from different rows. – Barmar Jul 11 '13 at 18:08
@Barmar Thank you. Super. I use Kohana ORM and I can't use construction in JOIN. Can it be more simplest? I made outer construction simpler, but can't do the same with inner. :( – Андрей Сердюк Jul 11 '13 at 18:49
You can use the UNION solution from spencer7593, but it requires knowing all the types so you can list all the subqueries. There's no way to simplify this join, this is a common idiom for getting the rows with the max or min value of some column within each group. – Barmar Jul 11 '13 at 18:55

score 0 · Answer 2 · answered Jul 11 '13 at 16:40

0

SELECT u1.id, u1.type, u1.date, u1.like, u1.dislike
from updates u1
inner join
(
   select type, max(date) as mdate
   from updates
   group by type
) u2 on u2.type = u1.type 
     and u2.mdate = u1.date

answered Jul 11 '13 at 16:40

juergen d

201,996
37
293
362

MySQL get last result for each where

2 Answers2