,

MySQL – Sorting records/users based on closeness to a age in SQL

Use: sort by users whose age is closest to a defined age. In the example below, 25 years.

User Age
1 25
3 23
4 26
5 27

Idea:


select abs(u_age-25) as age_difference,
       u_age
  from users
 order by age_difference, u_age;

The MySQL function abs ensures that the subtracted result is always positive.
The date of birth is what’s commonly stored in the database, so the query will look as follows:


select abs((now() - u_date_of_birth) - 25) as age_difference,
       now() - u_date_of_birth as u_age
  from users
 order by age_difference, u_age;

25 can be replaced by any age to align the results by.


Comments

Leave a Reply

Your email address will not be published. Required fields are marked *