Question 234037 in Mysql Question posted in
The official MySQL documentation can be found here.

Mysql – MAX primary and secondary?

With the below data example I want to group by the groupcol and within the group order by the val col and if no val exists secondary order by the ID.

Understand the first MAX value (sql below) but how to achieve the secondary MAX if no VAL exists?

SELECT ID,MAX(VAL), GROUPCOL FROM table GROUP BY GROUPCOL
ID VAL GROUPCOL
1 10 1
2 2
3 2
4 3
5 3
6 9 1
7 1

Would like to return this

ID VAL GROUPCOL
1 10 1
3 2
5 3
Tags:

2

Answers


  1. 0

    You need to specify which value you want to return for the column ID, after you group by GROUPCOL. MySQL has the choice to return either one of the values 1,6,7 for GROUPCOL=1. you should specify MIN() when you want 1, or MAX when you want to return 7.

    But when you say: "I want to group by the groupcol and within the group order by the val col and if no val exists secondary order by the ID.", you have to do something like:

    SELECT
   CASE WHEN MAX(VAL) IS NOT NULL THEN MIN(ID) ELSE MAX(ID) END as ID,
   MAX(VAL),
   GROUPCOL
FROM mytable
GROUP BY GROUPCOL;

    output:

    ID MAX(VAL) GROUPCOL
    1 10 1
    3 null 2
    5 null 3

    see: DBFIDDLE

    Login or Signup to reply.
  2. 0

    This is a typical use-case for row_number

    select ID, VAL, GROUPCOL
from (
  select ID, VAL, GROUPCOL, 
      Row_Number() over(partition by GROUPCOL order by VAL desc, ID desc) rn
  from t
)t
where rn = 1
order by rn;

    DbFiddle example

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search