ORDER BY with priority

user_11591189 0
Viewed 58

I want to use ORDER BY on uniqueid with priority. My table looks like this :

+-------------+------------------+
| disposition |     uniqueid     |  
+-------------+------------------+
| ANSWERED    | 1595409523.22546 |  
| NO ANSWER   | 1595409523.22546 |  
| BUSY        | 1595409523.22546 | 
| BUSY        | 1595406475.22391 |  
| NO ANSWER   | 1595406475.22391 |  
| BUSY        | 1595406475.22391 |  
+-------------+------------------+

I want for example on the ID: 1595409523.22546 to appear that the line where there is 'ANSWER', if there is no 'ANSWER' I want that 'NO ANSWER' is displayed otherwise BUSY etc ... The result should be :

+-------------+------------------+--+
| disposition |     uniqueid     |  |
+-------------+------------------+--+
| ANSWERED    | 1595409523.22546 |  |
| NO ANSWER   | 1595406475.22391 |  |
|             |                  |  |
+-------------+------------------+--+

Could you help me ? Thank you !

mysql
2 Answers

To check, for each uniqueid, a ranked list of dispositions and return the lowest ranked one found, or the lexically least disposition if none from the ranked list is found:

select
    if(min(find_in_set(disposition, 'ANSWERED,NO ANSWER,BUSY')),elt(min(find_in_set(disposition, 'ANSWERED,NO ANSWER,BUSY')),'ANSWERED,NO ANSWER,BUSY'),min(disposition)) disposition,
    uniqueid
from yourtable
group by uniqueid
ysth_173890

If your MySql version is 8.0+ and supports window functions then you can use ROW_NUMBER():

select t.disposition, t.uniqueid
from (
  select *,
    row_number() over (partition by uniqueid 
                       order by field(disposition, 'ANSWERED', 'NO ANSWER', 'BUSY')) rn
  from tablename                                    
) t
where t.rn = 1

For earlier versions you can use conditional aggregation:

select case 1
         when max(disposition = 'ANSWERED') then 'ANSWERED'
         when max(disposition = 'NO ANSWER') then 'NO ANSWER'
         when max(disposition = 'BUSY') then 'BUSY' 
       end disposition,
       uniqueid  
from tablename
group by uniqueid

See the demo.
Results:

> disposition |         uniqueid
> :---------- | ---------------:
> ANSWERED    | 1595409523.22546
> NO ANSWER   | 1595406475.22391
user_104988280
Related