What happened to the Messenger Plus! forums on msghelp.net?
Shoutbox » MsgHelp Archive » Skype & Technology » Tech Talk » Mysql Help

Mysql Help
Author: Message:
wj
Former Admin
*****

Avatar
I aim to misbehave.

Posts: 2224
Reputation: 52
39 / – / Flag
Joined: Mar 2002
O.P. Mysql Help
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.

This post was edited on 04-27-2007 at 05:49 AM by wj.
"A towel is about the most massively useful
thing an interstellar hitchhiker can have."
04-27-2007 05:22 AM
Profile PM Find Quote Report
John Anderton
Elite Member
*****

Avatar

Posts: 3908
Reputation: 80
37 / Male / Flag
Joined: Nov 2004
Status: Away
RE: Mysql Help
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 :)
[

KarunAB.com
]

[img]http://gamercards.exophase.com/459422.png[
/img]
04-27-2007 06:18 AM
Profile E-Mail PM Web Find Quote Report
wj
Former Admin
*****

Avatar
I aim to misbehave.

Posts: 2224
Reputation: 52
39 / – / Flag
Joined: Mar 2002
O.P. RE: Mysql Help
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?
"A towel is about the most massively useful
thing an interstellar hitchhiker can have."
04-27-2007 06:28 AM
Profile PM Find Quote Report
John Anderton
Elite Member
*****

Avatar

Posts: 3908
Reputation: 80
37 / Male / Flag
Joined: Nov 2004
Status: Away
RE: Mysql Help
Yeah it does. Thanks wj :)
[

KarunAB.com
]

[img]http://gamercards.exophase.com/459422.png[
/img]
04-27-2007 06:35 AM
Profile E-Mail PM Web Find Quote Report
« Next Oldest Return to Top Next Newest »


Threaded Mode | Linear Mode
View a Printable Version
Send this Thread to a Friend
Subscribe | Add to Favorites
Rate This Thread:

Forum Jump:

Forum Rules:
You cannot post new threads
You cannot post replies
You cannot post attachments
You can edit your posts
HTML is Off
myCode is On
Smilies are On
[img] Code is On