ORDER BY with priority

Question

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 !

forpas · Answer

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

ysth · Answer

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

ORDER BY with priority

2 Answers

Add your own answers!

Ask a Question