How to Get Max Price Based on Latest Date From group by query

Question

Example Records :

|name  |price |source|lastest_update|
|name A| 20.00|att   |04/10/2019 00:00:00|
|name A| 30.00|att   |04/11/2019 02:00:00|
|name A| 50.00|sprint|04/10/2019 01:00:00|
|name A| 40.00|sprint|04/11/2019 21:00:00|

Basically if we're using group by "group by name" the price that we'll get is the first one of the records, it's $20, but i want to get the max price based on lastest_update (date). So the results will be :

|name  |att_price|sprint_price|
|name A|  30.00  |  40.00     |

My query

SELECT 
MAX(WHEN source = 'att' THEN price ELSE 0 END) as att_price,
MAX(WHEN source = 'sprint' THEN price ELSE 0 END) as sprint_price
FROM table GROUP BY name;

Thank you very much.

`SELECT MAX(WHEN source = 'att' THEN price ELSE 0 END) as att_price, MAX(WHEN source = 'sprint' THEN price ELSE 0 END) as sprint_price FROM table GROUP BY latest_update DESC;` something like this? — xmaster, Apr 11 '19 at 13:11
Possible duplicate of [Retrieving the last record in each group - MySQL](https://stackoverflow.com/questions/1313120/retrieving-the-last-record-in-each-group-mysql) — Conspicuous Compiler, Apr 17 '19 at 19:18

nice_dev · Accepted Answer · 2019-04-17T19:08:11.687

0

Extract date from your timestamp column latest_update and apply max() to get the latest date. Now, you can just add a where condition to filter rows only having this max date.

select name,
MAX(case WHEN source = 'att' THEN price ELSE 0 END) as att_price,
MAX(case WHEN source = 'sprint' THEN price ELSE 0 END) as sprint_price
FROM test 
where date(latest_update) = (select max(date(latest_update)) from test)
GROUP BY name;

Demo: https://www.db-fiddle.com/f/43Uy7ocCKQRqJSaYHGcyRq/0

Update:

Since you need group by for each source according to individual source latest_update column, you can use the below SQL:

select t1.name,
    max(
        case 
            when t1.source = 'att' then t1.price
            else 0
        end
      ) as att_price,
     max(
        case 
            when t1.source = 'sprint' then t1.price
            else 0
        end
      ) as sprint_price
from test t1
      inner join (
        select name,source,max(latest_update) as latest_update
        from test
        group by name,source) t2
      on t1.name = t2.name and t1.source = t2.source
      and t1.latest_update  = t2.latest_update
group by t1.name;

Demo: https://www.db-fiddle.com/f/qwBxizWooVG7AMwqKjewP/0

edited Apr 17 '19 at 19:08

answered Apr 11 '19 at 13:58

nice_dev

17,053
2
21
35

1

yup thank you very much, it doesn't work for some case, but i already improve based on your answer. Thanks :) – Rizal Yogi Pratama Apr 16 '19 at 06:13
@RizalYogiPratama Glad to help but can you let me know the case for which it fails, so that I can improve my answer? – nice_dev Apr 16 '19 at 06:15
technically it will not show if any last_date that less than max (last_update) date Example: max last_date is "04/11/2019" from att, but there is some source that only updates once a month, ex: "04/01/2019" from sprint, this data from "04/01/2019" will not show. basically, in every source data have different max last_update. Thanks – Rizal Yogi Pratama Apr 16 '19 at 08:16
@RizalYogiPratama Ok, but this wasn't pretty clear from your question as it's ambiguous. Anyway, what if 2 different sources having max dates had 2 different names? – nice_dev Apr 16 '19 at 08:54
1

ah i see, my bad. Still one the same name. but the price that we'll get should be the latest from the (last_update) in every source but in the same name. – Rizal Yogi Pratama Apr 17 '19 at 09:08

How to Get Max Price Based on Latest Date From group by query

1 Answers1