How to group records based on condition in MySQL (group_by)

Question

I have users table, each user have multiple records. I need to fetch the latest inserted records of each user. How is it possible using MySQL

users table

id      name   value    updated_at

1       abc    123      2011-02-03
2       xyz    qwe      2011-04-03
3       abc    asd      2011-08-08
4       xyz    poi      2011-08-07

My output should be

id      name   value    updated_at

3       abc    asd      2011-08-08
4       xyz    poi      2011-08-07

I used Queries like

 select id,name,value,updated_at  from users group by name having max(updated_at)

 select id,ep_name,position,max(updated_at)  from test1 group by ep_name having max(updated_at)

But I was not able to get the desired result. I get the max(updated_at) value but not able to get the matching row. Please help

Note: Can this be done without using sub-query. Please help

Thanks in advance

Answer 1

You are right Mr. Amal you will not get the answer what you have specified above.Query by Dan will return with all results.

Try this definitely you will be on your way

SELECT  test.* FROM  test INNER JOIN (SELECT MAX(updated_at) AS `updated_at` FROM test GROUP BY  name) max_users
ON
  test.updated_at = max_users.updated_at

Second option for you:

SELECT  * FROM  test WHERE updated_at IN(SELECT MAX(updated_at) FROM test GROUP BY  name)

which is much simpler than first one

Answer 2

SELECT
  users.id,users.name, users.`value`, users.`updated_at`
FROM
  users
INNER JOIN
(
 SELECT name, MAX(updated_at) AS `updated_at`
 FROM
  users
 GROUP BY
  name
) max_users
ON
  users.name = max_users.name
AND
  users.updated_at = max_users.updated_at

Dan thanks a lot... :) small change in group by to match my output

Answer 3

No, this cannot be done without a subquery. A subquery is part of a single query.

SELECT
  users.*
FROM
  users
INNER JOIN
  (
    SELECT
      id,
      MAX(updated_at) AS `updated_at`
    FROM
      users
    GROUP BY
      id
  ) max_users
ON
  users.id = max_users.id
AND
  users.updated_at = max_users.updated_at