Records for max value across N columns in group

Question

This is an extension of the typical "max value per group" solved like so: https://stackoverflow.com/a/12102216/510144

What I need is 1 step further - getting the row with the max values across multiple columns in a cascading fashion.

Given:

id | effective_time | revision | score
 1 |              0 |        0 |     5
 1 |              1 |        0 |    10
 1 |              1 |        1 |    15
 1 |              2 |        0 |    20

I need:

1 |               1 |        1 | 15

In plain english: what's the value for item with id 1 where the max effective_time is less than 2 for the greatest revision for that effectime_time

please explain the result a bit. – Ankit Bajpai Jul 29 '19 at 19:18 — Ankit Bajpai, Jul 29 '19 at 19:18
Easy in MySQL 8.x. Are you using MySQL 5.x or 8.x? – The Impaler Jul 29 '19 at 19:18 — The Impaler, Jul 29 '19 at 19:18
What happens if there are ties? – The Impaler Jul 29 '19 at 19:18 — The Impaler, Jul 29 '19 at 19:18

score 1 · Answer 1 · answered Jul 29 '19 at 19:25

1

Assuming there are no ties (only a single row with the maximum value) you can do:

select *
from napoleon
where (effective_time, revision) < (2, 0)
order by effective_time desc, revision desc
limit 1

Result:

id  effective_time  revision  score
--  --------------  --------  -----
1                1         1     15

For reference, the data script I used to test is (you didn't mention the table name so I used "napoleon"):

create table napoleon (
  id int,
  effective_time int,
  revision int,
  score int
);

insert into napoleon (id, effective_time, revision, score) values
  (1, 0, 0, 5),
  (1, 1, 0, 10),
  (1, 1, 1, 15),
  (1, 2, 0, 20);

answered Jul 29 '19 at 19:25

The Impaler

45,731
9
39
76

Is (1,5) less than (3,4)? – Strawberry Jul 29 '19 at 21:06
@Strawberry `select case when (1, 5) < (3, 4) then 'yes' else 'no' end` -> yes. Tested in MySQL 5.5 and 8.0. – The Impaler Jul 30 '19 at 12:23
I was asking whether it was logically less rather than mathematically less – Strawberry Jul 30 '19 at 13:05

score 0 · Answer 2 · answered Jul 29 '19 at 19:21

If I understand correctly, you can use a correlated subquery:

select t.*
from t
where (effective_time, revision) = (select t2.effective_time, t2.revision
                                    from t t2
                                    where t2.id = t.id and
                                          t2.effective_time < 2 and
                                    order by t2.id desc, effective_time desc, revision desc
                                   );

Records for max value across N columns in group

2 Answers2