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 have more than 3 milions rows. There some rows having same values in the first_origin_id, delivery_id fields. If there're records with same first_origin_id, delivery_id but different source, I only want to select those records 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 performace?

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