distinct by column and ordered by date?

boorsa · #1 (**permalink**) 02-07-11, 06:53

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?

r937 · #2 (**permalink**) 02-07-11, 06:56

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

boorsa · #3 (**permalink**) 02-07-11, 08:10

Quote:

Originally Posted by r937

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

Internet Services

Web Development

Digital Marketing

Consumer Tech