Sort people by birth year closeness

[jasongr]

Hi people

I have been struggling with a query that I am trying to run
I have a table which stores information about people

The table definition is:

CREATE TABLE `people` (
  `Individual_ID` int unsigned not null,  
  `BirthYear` int default '',
  PRIMARY KEY  (`Individual_ID`),
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

The table holds for every person, his birth year
Given a specific person, I would like to get up to 3 other people from the table, ordered according to their closeness in birthyear to that person
That is order all the people according to how close their birth year is to that person

Here is what I mean by birth year closeness
assume that person 1 was bon on 1970
and we have the following people in the database:
person 2, born in 1973
person 3, born in 1966
person 4, has an empty birthyear
person 5, born in 1970
person 6, born in 1980

The 3 people returend will be (in that order)
person 5, born in 1970 (0 years apart)
person 2, born in 1973 (3 years apart)
person 3, born in 1966 (4 years apart)

People with an empty birthyear should be considered last

I would like to get the results in a single query
I thought of something along the line of: for each considered person, subtract his birthyear from the
birth year of the selected person and take absolute value of that
now order the value according to decreasing value. So people with absolute value of 0 will be first

any help would be appreciated, as I am struggling
with the order by clause

thanks

[r937]

I thought of something along the line of: for each considered person, subtract his birthyear from the
birth year of the selected person and take absolute value of that
now order the value according to decreasing value. So people with absolute value of 0 will be first

that's exactly how you do it

[shn]

ORDER BY?

But honestly, as far as i know from what you said, it doesn't really make sense to save the year of birth to a different table than where the users are stored, as there really can't be more than one value for birth year..

[Mr_Money]

Why dont you just order them in the order clause. Im assuming that people with the same year are allowed, so why not justr

SELECT id, birthyear
FROM people
WHERE birthyear >= <yourSelectedYear>
ORDER BY birthyear ASC LIMIT 3
d

EDIT
Oh I guess I didnt read the question clearly enough. I see you need people close regardless if they were before or after in years.

[Mr_Money]

whoah, you guys are quick on the draw here

[jasongr]

here is the solution I found:

SELECT Individual_ID
FROM people  
WHERE Individual_ID <> $selectedPersonID
ORDER BY case when (BirthYear = '') then 100000 else abs(BirthYear - $selectedPersonBirthYear) end
LIMIT 0, 3

[r937]

please allow me to suggest a few minor changes:

SELECT Individual_ID
     , abs(BirthYear 
               - $selectedPersonBirthYear) as years_diff
  FROM people  
 WHERE Individual_ID <> $selectedPersonID
ORDER 
    BY coalesce(years_diff,10000)
LIMIT 0, 3

[jasongr]

Hi r937
Thanks for the tip

can you explain what is the difference between your method and mine?

[r937]

your method only listed individuals, it didn't tell you the age diff

as for the ORDER BY, have a look at the manual for the COALESCE function

[jasongr]

I read abou the COALESCE function:

Returns the first non-NULL value in the list, or NULL if there are no non-NULL values.

I have 2 questions:
1) will that function also handle cases of people with an empty birth year?
2) is the method of using COALESCE more efficient than my order by clause?
or is it just more readable?

regards

[guelphdad]

You shouldn't be inserting a blank record when no information is given. You should be inserting NULLS. Inserting a blank and you are actually indicating a value, an empty string. If you use NULLS then you are indicating an absence of known value.

[r937]

1) will that function also handle cases of people with an empty birth year?

that's exactly what it will do -- that's what it's for


2) is the method of using COALESCE more efficient than my order by clause?
or is it just more readable?

yes