DISTINCT nor GROUP BY doesn't work to return unique values-CodePudding

SELECT DISTINCT order_reference as barcode,
    mth.location as location,
    mth.last_update_ts as updated
FROM sales_order_header soh
INNER JOIN manufacturing_tracking_history mth
    ON soh.id = mth.sales_order_header_id
GROUP BY barcode, location, updated
ORDER BY updated DESC
LIMIT 3000;

     barcode                           location             updated
0     BR2MGK    Tracking.Tracking.Tracking.TT03 2020-09-16 17:18:02
1     BR2MGK    Surfacing.Blocking.Blocking.AB8 2020-09-16 17:25:53
2     BR2MGK    Surfacing.Blocking.Blocking.AB8 2020-09-16 17:27:43
3     BR2MGK     Surfacing.Blocking.Blocking.C6 2020-09-16 17:37:23
4     BR2MGK    Tracking.Tracking.Tracking.BKCT 2020-09-16 17:41:13

I need only latest updated location from each barcode but I can't achieve that. Please help I am a newbie.

CodePudding user response：

I think you can do that by using Row_number() like this:

select barcode, location, updated from (
SELECT order_reference as barcode,
    mth.location as location,
    mth.last_update_ts as updated,
row_number() over(partition by order_reference order by mth.last_update_ts desc) rw
FROM sales_order_header soh
INNER JOIN manufacturing_tracking_history mth
    ON soh.id = mth.sales_order_header_id
) t
where t.rw=1
limit 3000;