CODEWITHSUNDEEP
mysqlsql
Stofke
Stofke

Reputation: 2958

MySQL query to get all highest id's

I have a table or queryresult like this.

    id  name url
    --- ---- ---
    1   AAA  http://aaa.com?r=123
    2   AAA  http://aaa.com?r=456
    1   BBB  http://bbb.com?r=xyz
    2   BBB  http://bbb.com?r=qsd
    3   BBB  http://bbb.com?r=fgh
    4   BBB  http://bbb.com?r=jkl
    1   CCC  http://ccc.com?r=a23
    3   CCC  http://ccc.com?r=bc6

What I actually want is to get all unique names with the highest id. So basically this.

    id  name url
    --- ---- ---
    2   AAA  http://aaa.com?r=456
    4   BBB  http://bbb.com?r=jkl
    3   CCC  http://ccc.com?r=bc6

What can I add or change to a query to get that result.

The proposed solutions do not work or return wrong results

OK finally I seem to have found it myself, this seems to work:

SELECT id, name, url
FROM yourtable
WHERE concat(id, name) IN 
(SELECT concat(max(id), name) FROM yourtable GROUP BY name)



id  name url
   --- ---- ---
   2   AAA  http://aaa.com?r=456
   4   BBB  http://bbb.com?r=jkl
   3   CCC  http://ccc.com?r=bc6

Upvotes: 1

Views: 1231

Answers (4)

Conrad Frix
Conrad Frix

Reputation: 52645

This solution works without using Concat

SELECT yt.id, 
    yt.name, 
    yt.url
FROM yourtable yt
INNER JOIN 
        (SELECT max(id) id ,
         name 
         FROM yourtable GROUP BY name) maxyt
ON yt.id  = maxyt.id
   AND yt.name = maxyt.name;

Using concat(id, name) may work but it suffers from two problems.

  1. Its definitely not SARGable (I'm not 100% sure my solution is)
  2. It will cause problems if someone puts in a name like 1AAA, Since the Concat(11,'AAA') = Concat (1,'1AAA')

Upvotes: 0

Vish
Vish

Reputation: 4492

Got it finally.

SELECT id, name, url, sum(id) as demosum FROM table_name GROUP BY name ORDER BY ID DESC

This should work.

Upvotes: 0

Vish
Vish

Reputation: 4492

Add GROUP BY id ORDER BY id ASC

Something like

SELECT id, name, url FROM yourtable GROUP BY id ORDER BY id ASC

Upvotes: 0

Marc B
Marc B

Reputation: 360572

SELECT id, name, url
FROM yourtable
WHERE id IN (SELECT max(id) FROM yourtable GROUP BY name))

The inner query fetches the highest ID number for every 'name' in the table, then the outer query uses those IDs to fetch the rest of the rows those IDs occur on.

Upvotes: 1

Related Questions