How to group by and order correctly

Question

Hi, I have a problem ordering my results correctly when using the group by. It seems to show the first entry in the database instead of the most recent in the group.

Example:

id(autoincrement) | name
1 | anne
2 | james
3 | anne
4 | brad

As you can see I have "anne" entered multiple times which is why I am using the group by. I would then like it to display the "anne" that is the most recent, which would be the entry "3". Instead it displays the first "anne"(1)

My query

"Select * FROM TABLE GROUP BY name ORDER BY id DESC

Any help would be greatly appreciated.

Answer 1

Try this; it will work:

SELECT * FROM TABLE
  INNER JOIN (SELECT MAX(id) AS id
                FROM TABLE
                group by name)
    ids ON TABLE.id = ids.id

Answer 2

A possible solution:

SELECT id, name
FROM TABLE
WHERE id IN (SELECT MAX(id) FROM TABLE GROUP BY name)

Answer 3

The problem is that you're selecting all the fields (using * is seldom a good idea) so each row is unique therefore there is nothing to group on.

Try:

SELECT 
  Name,
  MAX(ID)
FROM
  TABLE
GROUP BY
  Name

Answer 4

Try

SELECT DISTINCT name
FROM table
ORDER BY id DESC

Answer 5

Building on another anwer already provided, this SQL will avoid having to GROUP BY Name:

SELECT 
  DISTINCT Name,
  MAX(ID)
FROM
  TABLE