MYSQL get first non null frequency with order by length ASC

Question

Sample Records

I need to get the first record which has a not null frequency order by length ASC. In case there is no not null frequency record, then fetch the top length ASC record.

Here is what I tried:

select word, length, frequency 
from `dataset` 
where `word` LIKE '%aeto%' 
   and `length` >= 3 
ORDER BY length ASC,frequency desc;

select word, length, frequency 
from `dataset` 
where `word` LIKE '%aeto%' 
   and `length` >= 3 
ORDER BY CASE WHEN frequency IS NULL THEN length
         ELSE frequency
         END
    , length asc, `frequency` desc

Final solution what I can think of in my mind is:

• Run query to get a record where not null frequency and order by length ASC. Incase we do not have one, then try the first query and get the first result.

Expected Result:

As per sample , I need PHAETON as result, If I omit PHAETON and PRAETORIAN (both has non null frequency), then I need PRAETOR as the result

Table Structure and Data : https://dbfiddle.uk/32lmcKAj

Answer 1

This is a job for row_number()

with numbered as 
(
    select *
      ,row_number() over (order by case when frequency is not null then 0 else 1 end, length) rn
    from dataset
    where word LIKE '%aeto%' 
        and length >= 3 
)
select word, length, frequency
from numbered
where rn = 1

See it work here:

https://dbfiddle.uk/nNyA00Lj

Other answers miss this because MySQL was about 9 years late to the Window function party.

---

I suppose we could also just limit to the first record:

select *
from dataset
where word LIKE '%aeto%' and length >= 3 
order by case when frequency is not null then 0 else 1 end, length
limit 1

https://dbfiddle.uk/SceXwXer

---

In either case, the trick is we don't actually care about the precise value of the frequency, as long as it's non-null, and so we use a case expression in the order by clause to ensure ALL the non-null frequency values are treated the same and sort ahead of all the NULL rows; then (and only then) we also sort by length.