SQL QUERY - NO DUPLICATE RESULTS

Question

I have the below table, named shop:

enter image description here

Can you suggest the query to have no duplicate "product" results associated with the most recent delivery date.

Thanks

Please do not post images of code, data, error messages, etc. - copy or type the text into the question. Please reserve the use of images for diagrams or demonstrating rendering bugs, things that are impossible to describe accurately via text. For more information please see the [Meta FAQ entry Why not upload images of code/errors when asking a question?](https://meta.stackoverflow.com/a/285557) — CFreitas, Mar 20 '21 at 12:46

score 0 · Answer 1 · answered Mar 20 '21 at 11:55

0

select product, max(delivery_date) from table
group by product

answered Mar 20 '21 at 11:55

Marko

803
9
13

score 0 · Answer 2 · answered Mar 20 '21 at 11:57

0

SELECT
product, 
MAX(delivery_date) as most_recent_delivery_date 
FROM table_name
GROUP BY product;

answered Mar 20 '21 at 11:57

Krzysztof K

736
4
19

score 0 · Answer 3 · answered Mar 20 '21 at 12:20

If you want just the product, you can use aggregation as in the other answers. If you want the complete row, then one method is:

select t.*
from (select t.*,
             row_number() over (partition by product order by delivery_date desc) as seqnum
      from t
     ) t
where seqnum = 1;

SQL QUERY - NO DUPLICATE RESULTS

3 Answers3