Select only those records that have same multiple values for a particular column if it's existing?

Question

Below is an example of my pricing_supports table with more than 3 millions rows. There some rows having same values in the first_origin_id, delivery_id fields. If there are records with same first_origin_id, delivery_id but different source, I only want to select those records where source = 0

ID code first_origin_id delivery_id source
1 A 10 20 0
2 B 10 20 1
3 C 11 21 1
4 D 12 22 0
5 E 12 22 1

I would like result like that:

ID code first_origin_id delivery_id source
1 A 10 20 0
3 C 11 21 1
4 D 12 22 0

How can I do for good performance?

Answer 1

By using ROW_NUMBER() and PARTITION BY first_origin_id, delivery_id then ORDER BY source, you can achieve your expected result.

So the query will be:

SELECT ID, code, first_origin_id, delivery_id, source FROM (
 SELECT ID, code, first_origin_id, delivery_id, source, 
 ROW_NUMBER() OVER (PARTITION BY first_origin_id, delivery_id ORDER BY source, ID) AS RowNumber
 FROM pricing_supports
) Q
WHERE Q.RowNumber = 1

Please find the Live Demo

• Dương Khoa: It's work, but not best way for performance. I think. thanks @Arulkumar