3

Imagine the following table:

Id      type       created                      flag
-----------------------------------------------------
1          1        2015-12-11 12:00:00          0
2          1        2015-12-11 12:10:00          0
3          2        2015-12-11 12:00:00          0
4          2        2015-12-11 12:10:00          0
5          3        2015-12-11 12:00:00          0
6          3        2015-12-11 12:10:00          0
--------------------------------------------------------

I want to write a query that will set the flag to 1 (TRUE) for each row with the most recent created date for each type grouping. Ex, the result should be:

Id      type       created                      flag
-----------------------------------------------------
1          1        2015-12-11 12:00:00          0
2          1        2015-12-11 12:10:00          1
3          2        2015-12-11 12:00:00          0
4          2        2015-12-11 12:10:00          1
5          3        2015-12-11 12:00:00          0
6          3        2015-12-11 12:10:00          1
--------------------------------------------------------

I know that 

UPDATE table
SET flag = TRUE
ORDER BY created DESC
LIMIT 1;

will set the flag for the most recent date, but I'm stuck on how to apply this for each group.

I've also tried:

UPDATE table t
INNER JOIN 
    (SELECT id, flag, MAX(created) as maxdate
    FROM table
    GROUP BY type) as m
SET flag = TRUE
WHERE m.id = t.id AND t.created = m.maxdate;
ryan
  • 1,451
  • 11
  • 27
  • Possible duplicate of [SQL: Find the max record per group](http://stackoverflow.com/questions/2657482/sql-find-the-max-record-per-group) – Strawberry Dec 12 '15 at 00:29
  • What was wrong with your edit attempt? Oh, you've slightly muddled up the subquery and I'd use an ON clause before the SET rather than a WHERE clause after it. – Strawberry Dec 12 '15 at 07:11

1 Answers1

0

Maybe try something like this:

UPDATE table SET flag = 1 
WHERE id IN(SELECT id FROM(SELECT id, type FROM table 
ORDER BY created DESC) AS t GROUP BY type t.type);
Matt
  • 1,298
  • 1
  • 12
  • 31
  • @Strawberry Show me your wersio and I willing to give you a plus :) – Matt Dec 12 '15 at 00:30
  • this is close but not quite, it is still updating the wrong record... possibly because it orders by date first, the groupings dont necessarily have the max created date – ryan Dec 12 '15 at 00:33
  • It should be `ORDER BY created DESC` and you are missing the table name after `UPDATE` but this works. Thank you very much. – ryan Dec 12 '15 at 00:46