CODEWITHSUNDEEP
mysqlgroup-by
ben
ben

Reputation: 1

How to group by and order correctly

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.

Upvotes: 0

Views: 550

Answers (5)

Tony
Tony

Reputation: 53

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

SELECT 
  DISTINCT Name,
  MAX(ID)
FROM
  TABLE

Upvotes: 0

Pete
Pete

Reputation: 1783

Try

SELECT DISTINCT name
FROM table
ORDER BY id DESC

Upvotes: 0

Murph
Murph

Reputation: 10190

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

Upvotes: 4

user212701
user212701

Reputation:

A possible solution:

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

Upvotes: 2

valli
valli

Reputation: 5931

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

Upvotes: 0

Related Questions