MySQL: get max row in each group when max value is not unique

user_2382483 0
Viewed 199

There are many questions out there close to this, but I can't find one with a solid example of how to do quite what I want. I need to get a single max row for each group when the maximum value is not unique within a group. Here's a table:

| id | source | name | message_time |
|----|--------|------|--------------|
| 1  | a      | cool | 2020-08-18   |
| 2  | a      | cool | 2020-08-18   |
| 3  | a      | neat | 2020-08-02   |
| 4  | b      | nice | 2020-08-19   |
| 5  | b      | wow  | 2020-08-17   |

For each source, I need a single full row associated with the maximum message_time. Since the max message time is not unique within a group, both of these are valid outputs:

| id | source | name | message_time |
|----|--------|------|--------------|
| 1  | a      | cool | 2020-08-18   |
| 4  | b      | nice | 2020-08-19   |

| id | source | name | message_time |
|----|--------|------|--------------|
| 2  | a      | cool | 2020-08-18   |
| 4  | b      | nice | 2020-08-19   |

When there are multiple candidates for max, I just want to randomly select a single row. How can I achieve this with a mysql query?

I'm using MySQL 5.7

Edit:

So I messed around some more and realized this works:

SELECT table.*
FROM (
  SELECT source FROM table
  GROUP BY source
) groups
LEFT JOIN table
ON id = (
  SELECT id FROM table
  WHERE source = groups.source
  ORDER BY message_time desc
  LIMIT 1
);

I think I even understand why it works, but I don't know what no good, very bad practices I am doing here. Also, can it be simplified?

mysql
2 Answers

To do this, you use the row_number window function, which requires mysql 8.0 or mariadb 10.2 or better.

select id, source, name, message_time
from (
    select id, source, name, message_time,
        row_number() over (partition by source order by message_time desc, rand()) as row_num
    from a_table
) a_table_with_row_numbers
where row_num=1;

On earlier versions, you can do this:

select
    0+substr(max(concat(prefix,id)),19) id,
    substr(max(concat(prefix,source)),19) source,
    substr(max(concat(prefix,name)),19) name,
    max(message_time)
from (
    select id, source, name, message_time, concat(message_time, lpad(1e8*rand(),8,'0')) prefix
    from a_table
) a_table_with_sortable_prefix_string
group by source;
ysth_173890 
 SELECT
  MAX(m.id) as id,
  x.source,
  x.message_time
FROM
  (SELECT
    source,
    MAX(message_time) AS message_time
  FROM
    source
  GROUP BY 
    source ) x
INNER JOIN source m ON m.source=x.source AND m.message_time=x.message_time
GROUP BY
  x.source,
  x.message_time;

This will select the source and the max(message_time),

and then select the max(id) for that source and message_time

EDIT: some typo's in the query, and a test added:

sampledata created using:

CREATE TABLE source (id INTEGER, source CHAR(1), name VARCHAR(10), message_time DATE);
INSERT INTO source VALUES
   (1,'a','cool','2020-08-18'),
   (2,'a','cool','2020-08-18'),
   (3,'a','neat','2020-08-02'),
   (4,'b','nice','2020-08-19'),
   (5,'b','wow','2020-08-17');

output of query:

+------+--------+--------------+
| id   | source | message_time |
+------+--------+--------------+
|    2 | a      | 2020-08-18   |
|    4 | b      | 2020-08-19   |
+------+--------+--------------+
user_7240390
Related