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!

What MySQL-Version do you use? and what is your primary key? — Paul Spiegel, Feb 18 '16 at 09:17
Doesn't your query work? I don't see a reason, why it shouldn't - Even thoug i wouldn't do it like that. — Paul Spiegel, Feb 18 '16 at 09:24
Looks like OP already had a working query but wanted the result to be ordered by sale_date again. One more line at the end (`ORDER BY sale_date DESC;`) would fix his problem. — Paul Spiegel, Feb 18 '16 at 10:41

score 1 · Answer 1 · answered Feb 18 '16 at 09:21

1

You can use a correlated query to select the max date of each one of them like this:

SELECT listing_id,transaction_title,image_thumb,sale_date
FROM sales t
WHERE (listing_id,sale_date) in (select s.listing_id,max(s.sale_date)
                                 from sales s
                                 where t.listing_id = s.listing_id
                                 group by s.listing_id)

answered Feb 18 '16 at 09:21

sagi

40,026
6
59
84

What about two sales on same date? – Paul Spiegel Feb 18 '16 at 09:28
With the same id? How would that happen? He said he want to the max date, if there are two is the same max date then unless he will tell us which one to take, we cant figure it out on our own. Although I don't think it will happen in this case @PaulSpiegel – sagi Feb 18 '16 at 09:30
That is why i asked for primary key. And my query would be : select .. where pk = (select pk .. order by .. limit 1) – Paul Spiegel Feb 18 '16 at 09:35
Then it's time to add an autoincrement pk :-) – Paul Spiegel Feb 18 '16 at 09:53
I will just assume that (listing_id, sale_date) is unique - so your query is correct :-) – Paul Spiegel Feb 18 '16 at 10:14

score 1 · Answer 2 · answered Feb 18 '16 at 09:39

You can use a derived table containing maximum dates per listing_id. If you INNER JOIN to this table you can get the exprected result set:

select t1.listing_id, transaction_title, image_thumb, sale_date
from mytable as t1
inner join (
   select listing_id, max(sale_date) max_date
   from mytable
   group by listing_id
) as t2 on t1.listing_id = t2.listing_id and sale_date = max_date

score 1 · Accepted Answer · answered Feb 18 '16 at 09:52

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;

Out of all suggested answers, this is the only one that returned the correct results, thank you, Usman! — Acidon, Feb 18 '16 at 10:16

score 0 · Answer 4 · answered Feb 18 '16 at 09:21

0

just use subquery to get the listing_id and it's max sale_date first and then use it to find out other column

select * 
  from table t0 
 where exists
   (select 1 from 
      (select listing_id,max(sale_date) as sale_date 
         from table group by listing_id) t1 
        where t1.listing_id = t0.listing_id and t1.sale_date = t0.sale_date)

answered Feb 18 '16 at 09:21

Raffaello.D.Huke

552
2
9

The aliases are recognized two levels down? – sagi Feb 18 '16 at 09:26

Dylan Su · Answer 5 · 2016-02-18T10:24:09.600

0

You can get expected result using correlated subquery.

SELECT
    s1.listing_id, s1.transaction_title, s1.image_thumb, s1.sale_date
FROM sales s1
WHERE ( s1.listing_id, s1.transaction_title, s1.image_thumb, s1.sale_date ) IN   
                (SELECT s2.listing_id, s2.transaction_title, s2.image_thumb, s2.sale_date
                 FROM sales s2
                 WHERE s2.listing_id = s1.listing_id
                 ORDER BY s2.sale_date DESC
                 LIMIT 1);

edited Feb 18 '16 at 10:24

answered Feb 18 '16 at 09:27

Dylan Su

5,975
1
16
25

What if the image didn't change and there are two list_id,image the same? see 230105831 in his example – sagi Feb 18 '16 at 09:31
Different lasting_id will go into different subqueries, since we have filter "WHERE s2.listing_id = s1.listing_id". – Dylan Su Feb 18 '16 at 09:33
Ok, but lasting_id(230105831) has two records with the same image_thumb , so both of them will be selected. – sagi Feb 18 '16 at 09:35
From my understanding, only one row should be selected. "| 230105831 | Title Version 32 | Img Style 31 | 2016-02-06 |" When there are two columns, we can't always find a row with both max(c1) and max(c2) and they may at different rows. Therefore, a logic for ORDER BY c1 DESC, c2 DESC is used. – Dylan Su Feb 18 '16 at 09:38
@Acidon Pls have a try to check if the answer meet your expectation. – Dylan Su Feb 18 '16 at 09:39
I didn't really read the subquery ... ORDER BY s2.transaction_title DESC is wrong, version 2 is bigger then version 12 in strings, in addition, by his output example you can understand he want the data that appear on the max data, no matter if its an older version or image , so I can tell you now, it doesn't meet his expectation.. – sagi Feb 18 '16 at 09:41
You need to add `transaction_title` in your where clause and subselect: `WHERE ( s1.listing_id, s1.transaction, s1.image_thumb ) IN (SELECT s2.listing_id, s2.transaction, s2.image_thumb ...`. – Paul Spiegel Feb 18 '16 at 09:46
@PaulSpiegel Even then, hypothetical , isn't it possible to have twice the same (id,trans,img) ? – sagi Feb 18 '16 at 09:49
@sagi It would'nt matter if ordering would be by sale_date. – Paul Spiegel Feb 18 '16 at 09:51
Why not? then two records will meet the condition and will be selected.. @PaulSpiegel – sagi Feb 18 '16 at 09:53
Oh.. you're right. Then it would be same result as your solution :-) – Paul Spiegel Feb 18 '16 at 09:54
To prevent selecting duplicates you might also want to use DISTINCT. – Paul Spiegel Feb 18 '16 at 09:56
@PaulSpiegel I put the s2.sale_date into the ORDER BY s2.sale_date DESC to avoid the duplicate. Just updated. Thanks. – Dylan Su Feb 18 '16 at 10:02
The order should be **only** by sale_date (see the original query). – Paul Spiegel Feb 18 '16 at 10:05

score -1 · Answer 6 · answered Feb 18 '16 at 09:34

-1

Because correlations between max values are not defined, you simply can get MAX values for each column:

SELECT
    listing_id,
    MAX(transaction_title) transaction_title,
    MAX(image_thumb) image_thumb,
    MAX(sale_date) sale_date
FROM
    sales
GROUP BY
    listing_id

answered Feb 18 '16 at 09:34

mitkosoft

5,262
1
13
31

That will not return what he asked for, he is not selecting the max image_thumb and transaction_title . And even if he did, Version 2 is bigger then Version 11 , so it won't work . – sagi Feb 18 '16 at 09:36
He is looking for "latest used version of transaction_title and image_thumb". And where we have Version 2? – mitkosoft Feb 18 '16 at 09:38
"latest used version" is not equal to "highest values" – Paul Spiegel Feb 18 '16 at 09:43
So what defines "latest used version" in this data in that case? sale_date or what? – mitkosoft Feb 18 '16 at 09:47
@PaulSpiegel is right, and he doesn't have version 2, but you now.. its dummy data, so you can assume he will have version 2 with his real data.(It doesn't matter your logic in incorrect) – sagi Feb 18 '16 at 09:48

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

6 Answers6