11 Vote

MySQL: Find next higher and lower number compared to a value

Question by NetGuy | 2012-07-25 at 12:06

I have a value in MySQL and would like to determine in a column the nearest smaller and the nearest larger value. For example, consider just the following values in a column:

Now, I want to get the next higher value of 10 (which would be 13) and the next lower value of 10 (which would be 8)t.

With the MySQL function MAX, of course, I can not find the values. Is there something like MAX BETWEEN or somethin like that, with which I can determine the next value from a baseline?

ReplyPositiveNegative

Stefan Trost

3Best Answer5 Votes

Suppose we have a table "tab" and we want to find out in the column "col" the next value to 10.

For the next smallest value our query looks like this:

SELECT MAX (DISTINCT col) 
FROM tab
WHERE col < 10;

For the next highest value our query looks like this:

SELECT MIN (DISTINCT col) 
FROM tab
WHERE col > 10;

Simply insert your desired value into the query.
2012-07-27 at 10:23

ReplyPositive Negative

Important Note

Please note: The contributions published on askingbox.com are contributions of users and should not substitute professional advice. They are not verified by independents and do not necessarily reflect the opinion of askingbox.com. Learn more.

Participate

Ask your own question or write your own article on askingbox.com. That’s how it’s done.

MySQL: Find next higher and lower number compared to a value

Related Topics

CSS Hacks for the Internet Explorer

Color Values: Convert RGB, CMYK, CMY, HSV, TColor and XYZ Values

How to resize Image before Upload in Browser

Send Form Input as an Array to PHP Script

MySQL: Regular Expressions in MySQL queries

HTML: Preassign HTML Form with Data

MySQL: Change minimum word length for full text search

Important Note

Participate

Participate

Topics

Info

About