Selct one record in case of duplicated values-CodePudding

I am trying to get only one value, in case of duplicated records, form a query . I tryied with a SELECT DISTINCT but I wasen't able to set a condition.

I am collecting trackings status updates in a db and I am settting timestamp on every record.

So a same tracking record can be added more then one time in the db but with different timestamp. See example:

tracking_number    courier_name  timestamp

AAAAAAAAAA         DHL           1661864760
AAAAAAAAAA         DHL           1661964760
BBBBBBBBBB         DPD           1661864760
BBBBBBBBBB         DPD           1661964760
CCCCCCCCCC         FEDEX         1661764760

I am trying to get only one record (in case of duplicated records) for tracking , the one with the highest timestamp.

The result should be like this with only one record for duplication, the one with the highest timestamp

tracking_number    courier_name  timestamp

AAAAAAAAAA         DHL           1661964760
BBBBBBBBBB         DPD           1661964760
CCCCCCCCCC         FEDEX         1661764760

I tryed:

SELECT DISTINCT tracking_number, courier_name, timestamp FROM db ORDER BY tracking_number;

but I am getting always the duplications.

Any suggestion ?

Thank you

CodePudding user response：

A performant one, a linear execution :)

SELECT tracking_number,courier_name,timestamp
FROM (
        SELECT 
        tracking_number, courier_name, timestamp, ROW_NUMBER() OVER (PARTITION BY tracking_number  ORDER BY tracking_number) AS rownumber 
        FROM playground.trackings t) t 
WHERE t.rownumber = 1;

CodePudding user response：

Assuming that records belonging to the same tracking number always also share the same courier, we can try a basic GROUP BY query here:

SELECT tracking_number, courier_name, MAX(timestamp) AS timestamp
FROM yourTable
GROUP BY tracking_number, courier_name;

On MySQL 8 , a more general solution uses ROW_NUMBER():

WITH cte AS (
    SELECT *, ROW_NUMBER() OVER (PARTITION BY tracking_number
                                 ORDER BY timestamp DESC) rn
    FROM yourTable
)

SELECT tracking_number, courier_name, timestamp
FROM cte
WHERE rn = 1;

CodePudding user response：

You can use: SELECT TOP 1 tracking_number, courier_name, timestamp FROM db ORDER BY tracking_number from FROM db ORDER BY tracking_number DESC.

When you use ORDER BY sintax, by default, the order is ascending (lower to highest), so you should define that the order you want is DESC (highest to lower).

Here are some exemples of the TOP sintax: SQL TOP, LIMIT and FETCH FIRST Examples

CodePudding user response：

Distinction is applied to your query based on each column you define after DISTINCT key word not only first one. So because of that you can not fetch your desired result.

Perform an inner select query instead:

select * 
from db
where tracking_number in (select distinct tracking_number
                          from db 
                          order by tracking_number)

Good Luck.