Stuck on MySQL query, id where same quote_num but max status and max version

Question

I have the following table:

id      quote_num   version     status
1*      1000        1           1
2       1001        1           -1
3*      1001        2           0
4*      1003        1           0
5       1005        1           0
6*      1005        2           1
7       1005        3           0
8*      1007        1           1
9       1007        2           -1
10      1007        3           -1
11      1005        4           0
12      1008        1           -1
13      1008        2           -1
14      1008        3           -1
15*     1008        4           -1

I would like to get the starred id's.

Where quote_num is the same it needs to pick the first id having first the highest status and then the highest version.

Eg. 1005 has only one at status of 1 so it would get id 6. 1008 has 4 at status of -1 so it gets highest version meaning id 15.

I have cracked my brain over this MySQL Query, trying to do this with groups and subqueries.

Eg. Select * from table where id in (select id from table order by status desc, version desc LIMIT 1);

Any suggestions?

Answer 1

With ROW_NUMBER() window function:

SELECT id, quote_num, version, status
FROM (
  SELECT *, ROW_NUMBER() OVER (PARTITION BY quote_num ORDER BY status DESC, version DESC) rn
  FROM tablename
) t
WHERE t.rn = 1

See the demo.
Results:

> id | quote_num | version | status
> -: | --------: | ------: | -----:
>  1 |      1000 |       1 |      1
>  3 |      1001 |       2 |      0
>  4 |      1003 |       1 |      0
>  6 |      1005 |       2 |      1
>  8 |      1007 |       1 |      1
> 15 |      1008 |       4 |     -1

Answer 2

Your method is rather close. You just need = and a correlated subquery:

Select t.*
from table t
where t.id = (select t2.id
              from table t2
              where t2.quote_num = t.quote_num
              order by status desc, version desc 
              limit 1
             );

This should be able to make use of an index on (quote_num, status desc, version desc).