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

​x
 
item_Id    name            value priority1        department         A      F1        department         B      T1        zone               12     F1        area             Africa   F2        department         A      F2        zone               12     F2        area              Africa  F2        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

 
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

 
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 rnfrom tablename )A where rn=1​

Advertisement

Answer