MySQL find first row base on multiple conditions

Suppose exists a table as follows:

id    B     C     D
1     2     3     4
1     2     4     3
2     1     2     4
2     3     1     5

For each id, i want to keep the row with maximum B, if B equals, then keep the row with maximum C. So the final result is,

id    B     C     D
1     2     4     3
2     3     1     5

how to realize it by SQL?

Answer

Maximum tasks can be solved with NOT EXISTS. You only want rows for which not exists a row with the same ID and a greater B or the same B and a higher C.

select *
from mytable t1
where not exists
(
  select null
  from mytable t2
  where t2.id = t1.id
  and 
  (
    t2.b > t1.b
    or
    (t2.b = t1.b and t2.c > t1.c)
  )
);

As of MyQL 8 we can simply rank our rows with RANK, DENSE_RANK or ROW_NUMBER instead:

select id, b, c, d
from
(
  select id, b, c, d, row_number() over (partition by id order by b desc, c desc) as rn
  from mytable
) t
where rn = 1;

Demo: https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=a54a906373e0dd00a2fb42c119186dd3