SELECT only one entry of multiple occurrences

greatest-n-per-group, group-by, mysql, select, sql

Solution

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

Problem

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

Original source