Mysql Question posted in
The official MySQL documentation can be found here.

MYSQL get first non null frequency with order by length ASC

Sample Records

I need to get the first record which has a not null frequency order by length ASC. Incase 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

Tags:

2

Answers


  1. 0

    You can substitute frequency null values by some latest value coalesce(frequence,0.0).
    Then order result BY coalesce(frequency,0.0) desc, length asc.

    See examples:

    Frequency is not null

    select word, length, frequency 
from `dataset` 
where `word` LIKE '%aeto%' 
   and `length` >= 3 
ORDER BY coalesce(frequency,0.0) desc, length asc
limit 1
    word length frequency
    PHAETON 7 2.56

    Frequency is null, take length for order

    select word, length, frequency 
from `dataset` 
where `word` LIKE '%chaet%' 
   and `length` >= 3 
ORDER BY coalesce(frequency,0.0) desc, length asc
limit 1
    word length frequency
    CHAETA 6 null

    Case when search word not exist in table

    select word, length, frequency 
from `dataset` 
where `word` LIKE '%notfound%' 
   and `length` >= 3 
ORDER BY coalesce(frequency,0.0) desc, length asc
limit 1
    word length frequency

    fiddle

    Login or Signup to reply.
  2. 0

    Combine two queries, one gets the first record with non-null frequency, the other gets the first record with null frequency. Then select the first one if it exists.

    SELECT word, length, frequency
FROM (
    (
    SELECT word, length, frequency, 1 AS pref
    FROM dataset
    WHERE frequency IS NOT NULL
    ORDER BY length
    LIMIT 1
    )
    UNION ALL
    (
    SELECT word, length, frequency, 2 AS pref
    FROM dataset
    WHERE frequency IS NULL
    ORDER BY length
    LIMIT 1
    )
) AS x
ORDER BY pref
LIMIT 1

    DEMO

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search