CODEWITHSUNDEEP
mysql
nebs
nebs

Reputation: 4989

MySQL: Get result greater than a number if it exists, but below if it doesn't?

I have a table, call it MyTable which has a column, say "myvalue".

I need a query which takes a number and returns a row based on some comparison with myvalue.

If number=myvalue then that is the correct row. If there is no match, then I need to take the value just above number. If that doesn't exist I need to take the value just below number.

So for example let's say the myvalue column has these values:

100
200
300
400
500

If I query with number=50 it will return the row with '100'.
If I query with number=100 it will return the row with '100'.  
If I query with number=101 it will return the row with '200'.
If I query with number=450 it will return the row with '500'.
If I query with number=570 it will return the row with '500'.

So far I have a query which will return either the matching value or the one right above it, but I don't know how to make it return the one right below if the one above doesn't exist?

This is what I do now:

SELECT * FROM MyTable WHERE myvalue >= 'number' ORDER BY myvalue ASC LIMIT 1

(of course replace 'number' with a value)

Any ideas? I can think of a brute force method but I'm sure there's something elegant that I just can't think of.

Upvotes: 0

Views: 386

Answers (2)

MRM
MRM

Reputation: 435

SELECT * FROM MyTable WHERE myvalue >= 'number' ORDER BY abs('number'-myvalue) ASC ,myvalue DESC LIMIT 1;

Upvotes: 0

Jomoos
Jomoos

Reputation: 13083

SELECT *
FROM (
      (
        SELECT *
        FROM MyTable
        WHERE myvalue >= 'number'
        ORDER BY myvalue ASC
        LIMIT 1
      )
      UNION 
      (
        SELECT *
        FROM MyTable
        WHERE myvalue <= 'number'
        ORDER BY myvalue DESC
        LIMIT 1
      )
    ) AS temp
ORDER BY myvalue DESC 
LIMIT 1

Upvotes: 1

Related Questions