CODEWITHSUNDEEP
mysql
Alexey Sh
Alexey Sh

Reputation: 43

select id from table with max date and max id if dates are the same

For an example I have MySQL table like:

id  category  date        value
1   a         2013-01-02  7
2   a         2013-01-01  2
3   a         2013-01-01  3
4   b         2013-01-01  1
5   b         2013-01-02  4
6   b         2013-01-03  5
7   c         2013-01-03  4
8   c         2013-01-03  8

I need select records with MAX(date) for each category, but if date is the same, with MAX(id). So, expected result must be:

id  category  date        value
1   a         2013-01-02  7
6   b         2013-01-03  5
8   c         2013-01-03  8

As you can see, dates for same category can be not sorted (like id=4,5).

I tried query like:

SELECT * FROM mytable t1
INNER JOIN 
(
   SELECT category, MAX(date) AS MAXDATE
   FROM mytable
   GROUP BY category
) t2
ON t1.category = t2.category
AND t1.date = t2.MAXDATE

It works fine, when we have no same dates in table. But for my case, it will return records with lowest ids:

   id  category  date        value
    1   a         2013-01-01  7
    6   b         2013-01-03  5
    7   c         2013-01-03  4

Upvotes: 4

Views: 6314

Answers (1)

A.D.
A.D.

Reputation: 1180

You are on the right track but you need to change your join. I think what you want is the following:

SELECT * FROM mytable t1
INNER JOIN 
(
   SELECT MAX(id) AS ID, MAX(date) AS MAXDATE
   FROM mytable
   GROUP BY category
) t2
ON t1.id = t2.ID
AND t1.date = t2.MAXDATE

As mentioned, the previous wont work for every use case. The following might.

SELECT a.*
FROM mytable AS a,
(
    SELECT category, date, MAX(id) AS ID
    FROM mytable
    GROUP BY category, date
) AS b,
(
    SELECT category, MAX(date) AS date
    FROM mytable
    GROUP BY category
) AS c
WHERE b.category = c.category
AND b.date = c.date
AND a.id = b.id

Upvotes: 4

Related Questions