0

I need to get the latest record from deli_order table. My current table is something like this

order_code       |      code    |     create_time      | status
1914311385030918    nebula74920   2019-05-10 17:12:30      Fail
1914311385030918    nebula74920   2019-05-12 04:16:30      Fail
1914311385030918    nebula74920   2019-05-13 10:01:30      Success

There are 3 records with same order_code and code, but with different status.

I need to generate only the successful transaction. Below is my query

select 
    a.order_code as 'TRANSACTION ID',
    max(a.create_time) as 'TRANSACTION DATE',
    a.code as 'ORDER ID',
    case
        when a.status='8' then 'Failed'
        when a.status='6' then 'Completed'
        when a.status='5' then 'To be reviewed'
        when a.status='3' then 'To be accepted'
        when a.status='2' then 'To be accepted'
    else 'N/A'
    end as 'BLUTAB STATUS',
    b.lp_sign_time as 'PICK UP TIME'   
 from deli_order a
 left join pg_send_package b on a.code = b.code and a.order_code = b.order_code
 where date_format(a.plat_create_time, '%Y-%m') = '2019-05'
 group by a.order_code, a.code;

I expected to get the transaction with success status. But this is what I got

order_code       |      code    |     create_time      | status
1914311385030918    nebula74920    2019-05-13 10:01:30   Fail

What should i change in my query?

H. D. U.
  • 161
  • 7
  • 1
    MAX(CASE...) have a nice day – Strawberry May 13 '19 at 05:22
  • Also `a.plat_create_time BETWEEN '2019-05-01 00:00:00' AND '2019-05-31 23:59:59'` is much more efficient (or `>= '2019-05-01 00:00:00' AND < '2019-06-01 00:00:00'`) – Strawberry May 13 '19 at 05:26
  • @Strawberry thank you so much! Care to explain why i need to use MAX(CASE...)? I still didnt get it – H. D. U. May 13 '19 at 05:32
  • In general, you must group by all non-aggregated columns in the SELECT. The corollary of this is that should aggregate all columns in the select that are not included in the GROUP BY. An exception is where there exists a functional dependency (as we have assumed is the case with table b) – Strawberry May 13 '19 at 05:38

0 Answers0