Shoutbox

So... I want to select 5 records from a database that are closest to a given number.

Example:

Here is my data:


1
2
3
4
5
6
7
8
9
10

I want to select the 5 closest records to 6.

So it would return 3, 4, 5, 6, 7.

How would I go about doing this with mysql.

Extra credit for using an order by to return the results ordered by closest to 5 ( 5, 4, 6, 3, 7 or 5, 6, 4, 7, 3).

EDIT:
http://forums.devshed.com/postgresql-help-21/sele...record-175109.html

Found what I needed. Might be useful for someone else down the road here.

I was thinking about something like that but I didn't really know the syntax
I'm not an expert at dbs but wont that return you just the numbers closest to your number? How do you get the order (yourval, yourval+1, yourval-1, yourval+2, yourval-2 and so on)
Some explanation would be great

Thats why you do the order by diff.

Notes are in { }
SELECT discountstandard.* {< selects all the fields from the table}, ABS(quantity - <your value>) {<Creates a positive value from the data in the DB - the value you are looking for. This gives you the distance between the value in the DB and the value you want to find} AS diff {places the value into a psudeo column} FROM discountstandard {The table} ORDER BY diff {Sorting by the distance from your desired number}  ASC LIMIT 5


Does that make sense?

Yeah it does. Thanks wj