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!
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;