How to make SQL JOIN return only one row with most recent status code?

Question

The following SQL statement:

SELECT f.id, 
       f.front, 
       f.back, 
       h.statusChangedIdCode, 
       h.whenCreated,
       h.historyIdCode 
FROM dpod_site_flashcards AS f 
   JOIN dpod_site_historyItems AS h ON f.id = h.itemId 
WHERE f.id = 216 
  AND historyIdCode = 'statusChanged' 
ORDER BY h.whenCreated

gives me the following output. How do I change it so that it returns only one line which has the most recent statusChangedIdCode?

Answer 1

SELECT f.id, 
       f.front, 
       f.back, 
       h.statusChangedIdCode, 
       h.whenCreated,
       h.historyIdCode 
FROM dpod_site_flashcards AS f 
   JOIN dpod_site_historyItems AS h ON f.id = h.itemId 
WHERE f.id = 216 
  AND h.historyIdCode = 'statusChanged' 
  AND h.whenCreated = (select max(whenCreated)
                       from dpod_site_historyItems h2
                       where h2.itemId = h.itemId
                         and h2.historyIdCode = 'statusChanged')
ORDER BY h.whenCreated

or if you have a modern DBMS supporting windowing functions:

SELECT f.id, 
       f.front, 
       f.back, 
       h.statusChangedIdCode, 
       h.whenCreated,
       h.historyIdCode 
FROM dpod_site_flashcards AS f 
   JOIN (
     select itemid, 
            historyIdCode,
            whenCreated,
            statusChangedIdCode,
            row_number() over (partition by itemid order by whenCreated desc) as rn 
     from dpod_site_historyItems 
     where historyIdCode = 'statusChanged' 
   ) h ON f.id = h.itemId AND h.rn = 1
WHERE f.id = 216 
ORDER BY h.whenCreated

Both cases also work when you select rows for more than one dpod_site_flashcards.id which the LIMIT solution would not do.

Answer 2

Tried this?

SELECT f.id, f.front, f.back, h.statusChangedIdCode, MAX(h.whenCreated), h.historyIdCode 
FROM dpod_site_flashcards AS f 
JOIN dpod_site_historyItems AS h ON f.id=h.itemId 
WHERE f.id = 216 AND historyIdCode='statusChanged' 
GROUP BY f.id, f.front, f.back, h.statusChangedIdCode, h.historyIdCode 

-EDIT

Oops, thanks @Gordon, didn't read the requirements closely enough. Here is some SQLFiddle that should do the job.

Here is the sniplet of the correct code -- include this in your WHERE clause:

h.whenCreated = (select max(whenCreated)
   from dpod_site_historyItems h2
   where h2.itemId = h.itemId)