Results 1 to 3 of 3
  1. #1
    Join Date
    Sep 2010
    Posts
    3

    Unanswered: distinct by column and ordered by date?

    I have a MySql table called "users":
    Code:
    id	name	age	date_joined
    --	------	----	-------------
    1	dan	21	2010-Nov-01
    2	josh	25	2010-Oct-25
    3	robbie	21	2010-Nov-03
    4	david	25	2010-Oct-05
    5	sally	18	2010-Nov-03
    6	ben	21	2010-Dec-01
    I need to select all records but when the age is the same, I need to get the last person that joined.
    for example:
    age "21", we have "dan", "robbie" and "ben" when "ben" was the last one joined at 01 Dec 2010.
    age "25" as another example should only pull out "josh".

    the result of the SQL query should be:
    Code:
    2	josh	25	2010-Oct-25
    5	sally	18	2010-Nov-03
    6	ben	21	2010-Dec-01
    what SQL query can give me what I need?

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Code:
    SELECT users.id
         , users.name 
         , users.age 
         , users.date_joined
      FROM ( SELECT age
                  , MAX(date_joined) AS last_date
               FROM users
             GROUP
                 BY age ) AS m
    INNER
      JOIN users
        ON users.age = m.age
       AND users.date_joined = m.last_date
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Sep 2010
    Posts
    3

    igaltrm Harry

    Quote Originally Posted by r937 View Post
    Code:
    SELECT users.id
         , users.name 
         , users.age 
         , users.date_joined
      FROM ( SELECT age
                  , MAX(date_joined) AS last_date
               FROM users
             GROUP
                 BY age ) AS m
    INNER
      JOIN users
        ON users.age = m.age
       AND users.date_joined = m.last_date
    I thank you very much , it works
    if only the query could be any simpler that would be even better
    but thanks again

Posting Permissions

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