I have one long query return like below table Could I use group by to achieve below result Basically, i would like to to group name , and the value is based on priority without join the query itself Answer You can use row_number()

Mysql group by get value base of another colum value

I have one long query return like below table

item_Id    name            value priority
1        department         A      F
1        department         B      T
1        zone               12     F
1        area             Africa   F
2        department         A      F
2        zone               12     F
2        area              Africa  F
2        area              Europe  T

Could I use group by to achieve below result

item_id   name            value
 1       department       B
 1       zone             12
 1       area            Africa
 2       department        A
 2       zone             12
 2       area             Europe

Basically, i would like to to group name , and the value is based on priority without join the query itself

Answer

You can use row_number()

select * from
(
select item_id, name, value,
  row_number() over(partition by item_id,name order by case when priority='T' then 1 else 2 end) as rn
from tablename 
)A where rn=1

Advertisement

Answer