ORDBY BY difference between two numbers

I have a database table with people's ages in. I would like to order
the results of a query on this table by the differnce between their age
and a target age.
eg. database
userID, age
1, 27
2, 28
3, 29
4, 30
5, 31
6, 32
7, 33
I would like to do something like SELECT userID FROM database ORDER BY
difference_from 30 ASC, userId ASC
and get something like..
4,3,5,2,6,1,7 as the result
Any ideas?Hello,
Try something like this:
SELECT userID FROM database ORDER BY ABS(age-30), userId
Note that using an expression in an ORDER BY clause is not supported by
ANSI-SQL99. It is an extension created by Microsoft and later adopted
in SQL:2003.
Razvan|||Correction: using an expression in an ORDER BY clause is not supported
by
ANSI-SQL92, but it is supported in SQL99.
Razvan|||You dont need to order by the difference as long as you are comparing age to
a costant value. Subtracting the same value from the age will not change
the order.
10 -5 = 5
9 -5 = 4
8 -5 = 3
7 -5 = 2
6 -5 = 1
5 -5 = 0
Just order by age. If you want to display the difference in addition to
ordering by it, just select age - 30.
Select UserID, age - 30 as Difference
from SomeTable
order by age asc, userid asc
<webmaster@.ukescorts.com> wrote in message
news:1141729033.746642.173110@.j52g2000cwj.googlegroups.com...
> I have a database table with people's ages in. I would like to order
> the results of a query on this table by the differnce between their age
> and a target age.
> eg. database
> userID, age
> 1, 27
> 2, 28
> 3, 29
> 4, 30
> 5, 31
> 6, 32
> 7, 33
> I would like to do something like SELECT userID FROM database ORDER BY
> difference_from 30 ASC, userId ASC
> and get something like..
> 4,3,5,2,6,1,7 as the result
> Any ideas?
>