SELECT only one entry of multiple occurrences

Question

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

Answer 1

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

Answer 2

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;

Answer 3

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.