[SOLVED] Order by closest

[jaymc]

Hi

 

I am trying to return 5 results of numbers that match a number I define. Kind of like a radius e,g

 

I define the number 37 and the database pulls out:

 

38

36

39

40

31

 

I am trying to do this with one query, I dont want to run two queries using ASC for the first and DESC for the second

 

Any ideas

[fenway]

That depends.. how evenly spread is the data?

[PFMaBiSmAd]

I would do something like take the absolute value of the difference between the given value and the column value, order by that abs value ASC and limit 5

[jaymc]

That depends.. how evenly spread is the data?

 

Its peoples ages, and there is no set pattern hence the suggested of ke the absolute value of the difference between the given value and the column value may not work

 

Basically if someone is 45 years old I want to pull out 5 people as close as possible to their age. Hence maybe 3 people are all aged 45 and  there others are 46 and 44

 

Those are the ones I want

 

Another case may be someone is 76 and the 5 closest are 77,72,72,71,69

[fenway]

Wait... so why would the difference work?

 

EDIT: Oopsm, meant "not work"...

[Ken2k7]

the absolute value of the difference between the given value and the column value may not work

fenway - read ^

[kickstart]

Hi

 

Can't see why taking the aboslute alue wouldn't work.

 

Something like this (not tested and probably some typos)

 

SELECT  Age, abs(Age - $PassedAge) AS AgeDifference

FROM SomeTable

Order By Age Difference

LIMIT 5

 

All it is doing it calculating the age difference in years, ordering by that and just bringing back the first 5 records.

 

All the best

 

Keith

[Ken2k7]

Keith,

I don't think that would work. AgeDifference would just be Age - some value. The returned result may not be values that are the closest to $PassedAge. For example:

 

if $PassedAge is 10, then your SQL can return AgeDifference as 1,2,3,4,5.

[kickstart]

Hi

 

That should bring back all the ages and the age differences. Ordering by the age difference should give the closest ones first and the limit should just bring back the first 5. You still have the age back (and might well do nothing with the age difference)

 

There is a typo though with an extra space on the order clause.

 

All the best

 

Keith

[PFMaBiSmAd]

Just try it The absolute value of the difference will give you how close the value is to the desired value. 0 = same age, 1 = +/- one year, 2 = +/- two years... Taking the rows with the 5 lowest values will give you the 5 closest in age.

[fenway]

the absolute value of the difference between the given value and the column value may not work

fenway - read ^

 

Sorry, typo ... I think there's nothing wrong with this approach... jaymc?

[jaymc]

SELECT  Age, abs(Age - $PassedAge) AS AgeDifference

FROM SomeTable

Order By Age Difference

LIMIT 5

 

Worked