CODEWITHSUNDEEP
mysqlsqlgreatest-n-per-group
developarvin
developarvin

Reputation: 5087

MySQL - How to get latest row value grouped by a certain field?

Lets say we have this table named table A

id         foreign_id   value
1             1         x
2             2         y
3             1         y
4             2         x
5             3         x

where id is the primary key

How do you get the latest rows(we are going to base this in the order of the id) grouped by foreign id? Basically, what I want to get is

id     foreign_id     value
3      1              y
4      2              x
5      3              x

Upvotes: 3

Views: 6220

Answers (4)

nopaws
nopaws

Reputation: 245

`SELECT MAX(id) AS ID, 
       foreign_id,
       value 
 FROM tbl
 GROUP BY  foreign_id ASC`

This ans is quite fine for small amount of result but if you use large amount of data then it might wrong value for only "value" fields. Another approach is better for you to get proper value and here is this code.

`SELECT MAX(id) AS ID, 
       foreign_id,
       value 
 FROM tbl
 WHERE id in ( SELECT MAX(id) FROM tbl )
 GROUP BY  foreign_id ASC`

Upvotes: 0

Meherzad
Meherzad

Reputation: 8553

Try this query 

SELECT max(id) AS ID, foreign_id FROM tbl
GROUP BY  foreign_id

If value is also needed then 

SELECT a.ID, a.foreign_id, a.value  
FROM tbl a,
(SELECT max(id) AS ID, foreign_id 
        FROM tbl GROUP BY foreign_id) b
WHERE a.id = b.id AND 
      a.foreign_id = b.foreign_id

FIDDLE

Upvotes: 2

sundar
sundar

Reputation: 1760

Try this query, 

SELECT t.id, t.foreign_id, t.value FROM #temp t
WHERE t.id IN (SELECT max(id) FROM #temp GROUP BY foreign_id)

Replace #temp with your actual table name.

Upvotes: 3

John Woo
John Woo

Reputation: 263693

SELECT  a.*
FROM    tableName a
        INNER JOIN
        (
            SELECT  foreign_ID, max(id) max_ID
            FROM    tableName
            GROUP   BY foreign_ID
        ) b ON  a.foreign_ID = b.foreign_ID AND
                a.ID = b.max_ID

OUTPUT

╔════╦════════════╦═══════╗
║ ID ║ FOREIGN_ID ║ VALUE ║
╠════╬════════════╬═══════╣
║  3 ║          1 ║ y     ║
║  4 ║          2 ║ x     ║
║  5 ║          3 ║ x     ║
╚════╩════════════╩═══════╝

Upvotes: 0

Related Questions