Search code examples
sqlmysqlmaxmysql-logic

Hierarchical MAX

Is there a way to get a row that has the MAX() when two fields are considered, giving one of them precedence. For example

ID     Column_A     Column_B
----------------------------
1        1              3
2        3              2
3        2              2
4        3              1

Would return

ID     Column_A     Column_B
----------------------------
2        3              2

Because both ID's 2 and 4 are MAX on Column_A, but #2 "wins" on Column_B.

What I'd like this query to do, in pseudocode:

If (It's the MAX on A, tied with nothing)
  Return it
Else If (It's tied for MAX on A and it's MAX [or tied] on B)
  Return it
Solution

  • You could try...

    SELECT *
    FROM mytable
    ORDER BY Column_A DESC, Column_B DESC
    LIMIT 1;

    (if I understand the question correctly).

    Edited as Matthew Purdon kindly suggested.