SQL - Grouping with order condition

Question

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

Answer 1

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