CODEWITHSUNDEEP
mysqlsqlselectgroup-bygreatest-n-per-group
GameDroids
GameDroids

Reputation: 5662

SELECT only one entry of multiple occurrences

Let's say I have a Table that looks like this:

id fk value
------------
1  1  'lorem'
2  1  'ipsum'
3  1  'dolor'
4  2  'sit'
5  2  'amet'
6  3  'consetetur'
7  3  'sadipscing'

Each fk can appear multiple times, and for each fk I want to select the last row (or more precise the row with the respectively highest id) – like this:

id fk value
------------
3  1  'dolor'
5  2  'amet'
7  3  'sadipscing'

I thought I could use the keyword DISTINCT here like this:

SELECT DISTINCT id, fk, value 
FROM table

but I am not sure on which row DISTINCT will return and it must be the last one. Is there anything like (pseudo)

SELECT id, fk, value 
FROM table 
WHERE MAX(id) 
FOREACH DISTINCT(fk)

I hope I am making any sense here :) thank you for your time

Upvotes: 5

Views: 14165

Answers (4)

jkp
jkp

Reputation: 45

You could also do

SELECT id, fk, value FROM table GROUP BY fk HAVING id = MAX(id)

I don't have mysql here, but it works in Sybase ASE

Upvotes: 0

Saharsh Shah
Saharsh Shah

Reputation: 29051

Try this: 

SELECT a.id, a.fk, a.value
FROM tableA a 
INNER JOIN (SELECT MAX(a.id) id, a.fk FROM tableA a GROUP BY a.fk
           ) AS b ON a.fk = b.fk AND a.id = b.id;

OR

SELECT a.id, a.fk, a.value
FROM (SELECT a.id, a.fk, a.value FROM tableA a ORDER BY a.fk, a.id DESC) AS a 
GROUP BY a.fk;

Upvotes: 2

Awais Qarni
Awais Qarni

Reputation: 18006

SELECT * 
  FROM table  
  WHERE id IN (SELECT MAX(id) FROM table GROUP BY fk)

Upvotes: 11

Aziz Shaikh
Aziz Shaikh

Reputation: 16524

Try this:

SELECT t.* FROM
table1 t
JOIN (
  SELECT MAX(id) as id
  FROM table1
  GROUP BY fk
) t1
ON t.id = t1.id

Inner query will give you highest id for each fk using MAX(). Then we join this inner table with your main table.

Upvotes: 1

Related Questions