SitePoint Sponsor

User Tag List

Results 1 to 12 of 12
  1. #1
    SitePoint Addict jasongr's Avatar
    Join Date
    Jul 2004
    Location
    usa
    Posts
    371
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Sort people by birth year closeness

    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:
    Code:
    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

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by jasongr
    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

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Enthusiast shn's Avatar
    Join Date
    Jan 2006
    Location
    Munich DE
    Posts
    79
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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..
    Patrick

  4. #4
    SitePoint Evangelist
    Join Date
    Jan 2005
    Posts
    502
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Why dont you just order them in the order clause. Im assuming that people with the same year are allowed, so why not justr
    Code:
    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.

  5. #5
    SitePoint Evangelist
    Join Date
    Jan 2005
    Posts
    502
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    whoah, you guys are quick on the draw here

  6. #6
    SitePoint Addict jasongr's Avatar
    Join Date
    Jul 2004
    Location
    usa
    Posts
    371
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    here is the solution I found:
    Code:
    SELECT Individual_ID
    FROM people  
    WHERE Individual_ID <> $selectedPersonID
    ORDER BY case when (BirthYear = '') then 100000 else abs(BirthYear - $selectedPersonBirthYear) end
    LIMIT 0, 3

  7. #7
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    please allow me to suggest a few minor changes:
    Code:
    SELECT Individual_ID
         , abs(BirthYear 
                   - $selectedPersonBirthYear) as years_diff
      FROM people  
     WHERE Individual_ID <> $selectedPersonID
    ORDER 
        BY coalesce(years_diff,10000)
    LIMIT 0, 3
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  8. #8
    SitePoint Addict jasongr's Avatar
    Join Date
    Jul 2004
    Location
    usa
    Posts
    371
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi r937
    Thanks for the tip

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

  9. #9
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  10. #10
    SitePoint Addict jasongr's Avatar
    Join Date
    Jul 2004
    Location
    usa
    Posts
    371
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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

  11. #11
    SitePoint Wizard guelphdad's Avatar
    Join Date
    Oct 2003
    Location
    St. Catharines, ON Canada
    Posts
    1,708
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.

  12. #12
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •