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 !
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
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