CODEWITHSUNDEEP
phpmysql
user1048676
user1048676

Reputation: 10066

Get next highest value and next lowest value from database

All, I've got a number of an ID and I'd like to get the next lowest id in my database and I'd also like to get the next greatest number. Is there a simple way to do this?

So say I have the following number sequence:

9 7 6 4 1

My current number is 6, then I'd like to select 7 for the next highest and 4 for the next lowest.

Also if there is no higher number then I'd like to just return the highest number and the same thing for the lower number. If there is no lower number then I'd like to just select the lower number.

Is there an easy way to do this?

Thanks!

Upvotes: 0

Views: 1930

Answers (1)

databyss
databyss

Reputation: 6488

This will get your two values:

SELECT MIN(number) AS next_number FROM table_name WHERE number > 6

SELECT MAX(number) AS last_number FROM table_name WHERE number < 6

If you need them in one query, you can use those as sub queries and select next_number and last_number

Upvotes: 5

Related Questions