SQL - Grouping with order condition

user_18650890 0
Viewed 63

I am new with SQL and have some data in table and want to make grouping based on column CLASS and STATUS with condition based on the value in both column. It will group based on the highest order

The Table shown below

Scenario 1

Table Source :

ID Class Status
001 Platinum ACTIVE
001 Gold ACTIVE
001 Silver ACTIVE
001 Regular ACTIVE

Output after Grouping:

ID Class Status
001 Platinum ACTIVE

Scenario 2

Table Source :

ID Class Status
001 Gold ACTIVE
001 Silver INACTIVE
001 Regular INACTIVE

Output after Grouping:

ID Class Status
001 Gold ACTIVE

So basically the grouping will be determined by the value of CLASS and Status

For CLASS the order of grouping is Platinum > Gold > Silver > Regular

For Status the order of grouping is ACTIVE > INACTIVE

Is there any way to do this?

Thanks

group-by sql
1 Answers

you can use sql case statement to define the order for each class and subject. then sort the resulting table as subquery.

select t1.ID, t1.class, t1.Status from (
    select ID, Class, Status (case when 'Platinum' then 1 when 'Gold' then 2 when 'Silver' then 3 else 4 end) as sort_order1,
        (case when 'active' then 1 else 2 end) as sort_order2
    from table1)
order by t1.sort_order1, t1.sort_order2
user_64290720
Related