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

SELECT word, length, frequency
FROM (
    (
        SELECT word, length, frequency
        FROM `dataset`
        WHERE `word` LIKE '%aeto%'
          AND `length` >= 3
          AND `frequency` IS NOT NULL
        ORDER BY `length` ASC, `frequency` DESC
        LIMIT 1
    )
    UNION
    (
        SELECT word, length, frequency
        FROM `dataset`
        WHERE `word` LIKE '%aeto%'
          AND `length` >= 3
          AND NOT EXISTS (
              SELECT 1
              FROM `dataset`
              WHERE `word` LIKE '%aeto%'
                AND `length` >= 3
                AND `frequency` IS NOT NULL
          )
        ORDER BY `length` ASC, `frequency` DESC
        LIMIT 1
    )
) AS combined_results
LIMIT 1;