MySQL sorting by date with GROUP BY

Question

My table titles looks like this

id |group|date                |title
---+-----+--------------------+--------
1  |1    |2012-07-26 18:59:30 | Title 1
2  |1    |2012-07-26 19:01:20 | Title 2
3  |2    |2012-07-26 19:18:15 | Title 3
4  |2    |2012-07-26 20:09:28 | Title 4
5  |2    |2012-07-26 23:59:52 | Title 5

I need latest result from each group ordered by date in descending order. Something like this

id |group|date                |title
---+-----+--------------------+--------
5  |2    |2012-07-26 23:59:52 | Title 5
2  |1    |2012-07-26 19:01:20 | Title 2

I tried

SELECT *
FROM `titles`
GROUP BY `group`
ORDER BY MAX( `date` ) DESC

but I'm geting first results from groups. Like this

id |group|date                |title
---+-----+--------------------+--------
3  |2    |2012-07-26 18:59:30 | Title 3
1  |1    |2012-07-26 19:18:15 | Title 1

What am I doing wrong? Is this query going to be more complicated if I use LEFT JOIN?

Answer 1

MySQL uses an dumb extension of GROUP BY which is not reliable if you want to get such results therefore, you could use

select id, group, date, title from titles as t where id = 
(select id from titles where group = a.group order by date desc limit 1);

In this query, each time the table is scanned full for each group so it can find the most recent date. I could not find any better alternate for this. Hope this will help someone.

Answer 2

Use the following query to get the most recent record from each group

SELECT 
T1.* FROM
(SELECT 
    MAX(ID) AS maxID
FROM
    T2
GROUP BY Type) AS aux
    INNER JOIN
T2 AS T2 ON T1.ID = aux.maxID ;

Where ID is your auto increment field and Type is the type of records, you wanted to group by.

Answer 3

I found this topic via Google, looked like I had the same issue. Here's my own solution if, like me, you don't like subqueries :

-- Create a temporary table like the output
CREATE TEMPORARY TABLE titles_tmp LIKE titles;

-- Add a unique key on where you want to GROUP BY
ALTER TABLE titles_tmp ADD UNIQUE KEY `group` (`group`);

-- Read the result into the tmp_table. Duplicates won't be inserted.
INSERT IGNORE INTO titles_tmp
  SELECT *
    FROM `titles`
    ORDER BY  `date` DESC;

-- Read the temporary table as output
SELECT * 
  FROM titles_tmp
  ORDER BY `group`;

It has a way better performance. Here's how to increase speed if the date_column has the same order as the auto_increment_one (you then don't need an ORDER BY statement) :

-- Create a temporary table like the output
CREATE TEMPORARY TABLE titles_tmp LIKE titles;

-- Add a unique key on where you want to GROUP BY
ALTER TABLE titles_tmp ADD UNIQUE KEY `group` (`group`);

-- Read the result into the tmp_table, in the natural order. Duplicates will update the temporary table with the freshest information.
INSERT INTO titles_tmp
  SELECT *
    FROM `titles`

  ON DUPLICATE KEY 
    UPDATE  `id`    = VALUES(`id`), 
            `date`  = VALUES(`date`), 
            `title` = VALUES(`title`);

-- Read the temporary table as output
SELECT * 
  FROM titles_tmp
  ORDER BY `group`;

Result :

+----+-------+---------------------+---------+
| id | group | date                | title   |
+----+-------+---------------------+---------+
|  2 |     1 | 2012-07-26 19:01:20 | Title 2 |
|  5 |     2 | 2012-07-26 23:59:52 | Title 5 |
+----+-------+---------------------+---------+

On large tables this method makes a significant point in terms of performance.

Answer 4

Use the below mysql query to get latest updated/inserted record from table.

SELECT * FROM 
(
  select * from `titles` order by `date` desc
) as tmp_table
group by `group`
order by `date` desc

Answer 5

If your id field is an auto-incrementing field, and it's safe to say that the highest value of the id field is also the highest value for the date of any group, then this is a simple solution:

SELECT   b.*
FROM     (SELECT MAX(id) AS maxid FROM titles GROUP BY group) a
JOIN     titles b ON a.maxid = b.id
ORDER BY b.date DESC 

Answer 6

Another approach is to make use of MySQL user variables to identify a "control break" in the group values.

If you can live with an extra column being returned, something like this will work:

SELECT IF(s.group = @prev_group,0,1) AS latest_in_group
     , s.id
     , @prev_group := s.group AS `group`
     , s.date
     , s.title
  FROM (SELECT t.id,t.group,t.date,t.title
          FROM titles t
         ORDER BY t.group DESC, t.date DESC, t.id DESC
       ) s
  JOIN (SELECT @prev_group := NULL) p
HAVING latest_in_group = 1
 ORDER BY s.group DESC

What this is doing is ordering all the rows by group and by date in descending order. (We specify DESC on all the columns in the ORDER BY, in case there is an index on (group,date,id) that MySQL can do a "reverse scan" on. The inclusion of the id column gets us deterministic (repeatable) behavior, in the case when there are more than one row with the latest date value.) That's the inline view aliased as s.

The "trick" we use is to compare the group value to the group value from the previous row. Whenever we have a different value, we know that we are starting a "new" group, and that this row is the "latest" row (we have the IF function return a 1). Otherwise (when the group values match), it's not the latest row (and we have the IF function returns a 0).

Then, we filter out all the rows that don't have that latest_in_group set as a 1.

It's possible to remove that extra column by wrapping that query (as an inline view) in another query:

SELECT r.id
     , r.group
     , r.date
     , r.title
  FROM ( SELECT IF(s.group = @prev_group,0,1) AS latest_in_group
              , s.id
              , @prev_group := s.group AS `group`
              , s.date
              , s.title
           FROM (SELECT t.id,t.group,t.date,t.title
                   FROM titles t
                  ORDER BY t.group DESC, t.date DESC, t.id DESC
                ) s
           JOIN (SELECT @prev_group := NULL) p
         HAVING latest_in_group = 1
       ) r
 ORDER BY r.group DESC

Answer 7

Well, if dates are unique in a group this would work (if not, you'll see several rows that match the max date in a group). (Also, bad naming of columns, 'group', 'date' might give you syntax errors and such specially 'group')

select t1.* from titles t1, (select group, max(date) date from titles group by group) t2
where t2.date = t1.date
and t1.group = t2.group
order by date desc

Answer 8

This page was very helpful to me; it taught me how to use self-joins to get the max/min/something-n rows per group.

In your situation, it can be applied to the effect you want like so:

SELECT * FROM
(SELECT group, MAX(date) AS date FROM titles GROUP BY group)
AS x JOIN titles USING (group, date);