CODEWITHSUNDEEP
mysqlgreatest-n-per-group
Edison Machado
Edison Machado

Reputation: 1410

How to select all the last row of some column scope in MySQL

I need a query to select the last rows of some column scope.

Example, the whole table:

----------------------
| ID | NAME | FOO_ID |
|--------------------|
| 1  | jQue |   2    |
| 2  | Prot |   2    |
| 3  | Node |   2    |
| 4  | Moo  |   1    |
| 5  | Scri |   1    |
----------------------

Rows returned by query, that returns all the "last row" in FOO_ID column scope:

----------------------
| ID | NAME | FOO_ID |
|--------------------|
| 3  | Node |   2    |
| 5  | Scri |   1    |
----------------------

Can someone help me in how to code this query?

Upvotes: 0

Views: 109

Answers (2)

ruakh
ruakh

Reputation: 183321

I'd use whichever of these you find most clear:

  • a1ex07's query

or

SELECT *
  FROM the_table
 WHERE id IN
        ( SELECT MAX(id)
            FROM the_table
           GROUP
              BY foo_id
        )
;

or

SELECT *
  FROM the_table a
 WHERE NOT EXISTS
        ( SELECT 1
            FROM the_table b
           WHERE b.foo_id = a.foo_id
             AND b.id > a.id
        )
;

or

SELECT a.*
  FROM the_table a
  LEFT
 OUTER
  JOIN the_table b
    ON b.foo_id = a.foo_id
   AND b.id > a.id
 WHERE b.id IS NULL
;

(My own preference is the version with IN, but you may feel differently.)

Upvotes: 1

a1ex07
a1ex07

Reputation: 37364

SELECT a.* 
FROM table_1 a
INNER JOIN (SELECT foo_id, max(id) as max_id FROM table_1 GROUP BY foo_id)b
ON b.max_id = a.id

Upvotes: 2

Related Questions