CODEWITHSUNDEEP
sqlpostgresql
danjuggler
danjuggler

Reputation: 1320

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

Upvotes: 6

Views: 3355

Answers (2)

shawnt00
shawnt00

Reputation: 17915

select
    id,
    case
        max(
            case max_type
                when 'C' then 3 when 'A' then 2 when 'B' then 1
            end
        )
        when 3 then 'C' when 2 then 'A' when 1 then 'B'
    end as max_type
from T
group by id

Translate to a value that an be ranked by max() and then translate back to the original value.

If you also want to order the result by that value then you could add:

order by
    max(
        case max_type
            when 'C' then 3 when 'A' then 2 when 'B' then 1
        end
    ) desc

Some platforms require the sorting column to be included in the output. I'm not sure if PostgreSql is one of those. And no objection to Gordon's answer but you'd have to use another window function to calculate the sort order if you need that too.

Upvotes: 10

Gordon Linoff
Gordon Linoff

Reputation: 1269623

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;

Upvotes: 3

Related Questions