danjuggler danjuggler - 4 years ago 174
SQL Question

Custom Order for Max()

I want to get the "max" character value for a column using a group by statement, except instead of the default alphabetical order, I want to set up a custom ordering that the max will use.

Table1:

ID | TYPE
-----+-------
1 | A
1 | B
1 | C
2 | A
2 | B


I want to group by ID and get max(type) in the order of C, A, B. Expected result:

ID | MAX_TYPE
-----+-----------
1 | C
2 | A

Answer Source

Instead of translating back and forth, use window functions:

select t.*
from (select t.*,
             row_number() over (partition by id
                                order by (case when type = 'C' then 1
                                               when type = 'A' then 2
                                               when type = 'B' then 3
                                          end) as seqnum
      from t
     ) t
where seqnum = 1;

Depending on what the values look like, you can also simplify this using string functions:

select t.*
from (select t.*,
             row_number() over (partition by id
                                order by position(type, 'CAB')) as seqnum
      from t
     ) t
where seqnum = 1;
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download