0

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?

0

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

  • It's work, but not best way for performance. I think. thanks @Arulkumar – Dương Khoa Sep 20 '18 at 9:30

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.