Select the lastest one of each result in MySQL

Question

Say if I have a table similar to this but including more columns and more rows (These are the only relevant ones):

+-------+----+
| name  | id |
+-------+----+
| james | 1  |
| james | 2  |
| james | 3  |
| adam  | 4  |
| max   | 5  |
| adam  | 6  |
| max   | 7  |
| adam  | 8  |
+-------+----+

How could I get it so that it would only show the max(id) from each name like:

+-------+----+
| name  | id |
+-------+----+
| adam  | 8  |
| max   | 7  |
| james | 3  |
+-------+----+

I currently just have this

"select * from table order by id desc"

but this just shows the latest ids. I only want to be able to see one of each name.

So basically show only the highest id of each name

It doesn't take much time really and makes the question much easier to read. — toxalot, Mar 01 '14 at 03:59
Based on your comments to Gordon Linoff's answer, you should read http://stackoverflow.com/questions/7745609/sql-select-only-rows-with-max-value-on-a-column — toxalot, Mar 01 '14 at 04:51

Gordon Linoff · Answer 1 · 2014-03-18T10:34:47.927

3

You would use aggregation and max():

select name, max(id)
from table t
group by name
order by max(id) desc
limit 40;

EDIT:

If you need select * with the highest id, then use the not exists approach:

select *
from table t
where not exists (select 1 from table t2 where t2.name = t.name and t2.id > t.id)
order by id desc
limit 40;

The "not exists" essentially says: "Get me all rows in the table where there is no other row with the same name and a higher id". That is a round-about way of getting the maximum row.

edited Mar 18 '14 at 10:34

answered Mar 01 '14 at 03:36

Gordon Linoff

1,242,037
58
646
786

I have tried this`"select name, max(id), comunicate, session_number, date from social group by name order by max(id) desc limit 40;"` but this picks the earliest id – maxisme Mar 01 '14 at 04:03
2

@Maximilian . . . If you have more columns, you should ask another question, with appropriate data and your desired results. That question is different from the one you asked. – Gordon Linoff Mar 01 '14 at 04:06

peterm · Answer 2 · 2014-03-01T07:33:30.653

0

One way to achieve this is to leverage a non-standard GROUP BY extension in MySQL

SELECT *
  FROM
(
  SELECT *
    FROM table1
   ORDER BY id DESC
) q
 GROUP BY name
 -- LIMIT 40

or another way is to grab a max id per name first and then join back to your table to fetch all other columns

SELECT t.*
  FROM
(
  SELECT MAX(id) id
    FROM table1
   GROUP BY name
   -- LIMIT 40
) q JOIN table1 t
    ON q.id = t.id
 ORDER BY name;

Output:

|  NAME | ID |
|-------|----|
|  adam |  8 |
| james |  3 |
|   max |  7 |

Here is SQLFiddle demo

edited Mar 01 '14 at 07:33

answered Mar 01 '14 at 07:28

peterm

91,357
15
148
157

OP wants results in descending order by `id` not `name`. – toxalot Mar 02 '14 at 23:24
I would not recommend the first query even though MySQL supports it. The values returned for non-aggregated columns not named in the GROUP BY expression are indeterminate. Although selecting from an ordered subquery does appear to solve this, I would not want to rely on this behavior. – toxalot Mar 04 '14 at 06:52

Select the lastest one of each result in MySQL

2 Answers2