Select the latest version based on date for each ID in MYSQL

Question

I have a table like:

+------------+-------------------+--------------+------------+
| listing_id | transaction_title | image_thumb  | sale_date  |
+------------+-------------------+--------------+------------+
| 226835186  | Title Version 11  | Img Style 11 | 2016-02-08 |
+------------+-------------------+--------------+------------+
| 226835186  | Title Version 11  | Img Style 12 | 2016-02-16 |
+------------+-------------------+--------------+------------+
| 228703248  | Title Version 21  | Img Style 21 | 2016-02-15 |
+------------+-------------------+--------------+------------+
| 228703248  | Title Version 22  | Img Style 22 | 2016-02-17 |
+------------+-------------------+--------------+------------+
| 228703248  | Title Version 23  | Img Style 21 | 2016-02-16 |
+------------+-------------------+--------------+------------+
| 230105831  | Title Version 31  | Img Style 31 | 2016-02-12 |
+------------+-------------------+--------------+------------+
| 230105831  | Title Version 32  | Img Style 31 | 2016-02-06 |
+------------+-------------------+--------------+------------+

I am trying to get a query of distinct listing_id with a latest used version of transaction_title and image_thumb. For the above table query output will be:

+------------+-------------------+--------------+------------+
| listing_id | transaction_title | image_thumb  | sale_date  |
+------------+-------------------+--------------+------------+
| 226835186  | Title Version 11  | Img Style 12 | 2016-02-16 |
+------------+-------------------+--------------+------------+
| 228703248  | Title Version 22  | Img Style 22 | 2016-02-17 |
+------------+-------------------+--------------+------------+
| 230105831  | Title Version 31  | Img Style 31 | 2016-02-12 |
+------------+-------------------+--------------+------------+

I've tried different combinations of select distinct, num_rows and max() but can't get the desired result.

Latest I've tried:

SELECT
    listing_id,transaction_title,image_thumb,sale_date
FROM (
    SELECT * FROM sales
    ORDER BY sale_date DESC
) AS transaction_title
GROUP BY listing_id

Please help!

Answer 1

You can use row_number base approach with data sorted descending first on listing_id and sale_date and then picking the rows with row_number 1. This will give you the required dataset. A query template for this approach is as follows:

SELECT  INVW.listing_id, INVW.transaction_title, INVW.image_thumb, INVW.sale_date 
FROM (
    SELECT  listing_id, transaction_title, image_thumb, sale_date
        ,@rank := if(@listing_id = listing_id or listing_id is null, @rank + 1, 1) as row_number 
        ,@listing_id := listing_id as dummy
    FROM <###REPLACE_ME_WITH_TABLE_NAME###>, (select @rank := 0,@listing_id := '') rank
    ORDER BY listing_id,sale_date DESC 
) INVW where INVW.row_number = 1;