Remove duplicates based on a criteria

[NickOnTheNet]

Hi all,
can someone help me with this please?

I need to remove any duplicates from Query: qu_combined_sp which have the highest Rank (highlighted).
The result I need is in Query: qu_combined_sp_result

Thanks for your time!!

Regards... Nick

 

[theDBguy]

Hi Nick,

What is the logic we can use to determine a duplicate?

 

[CJ_London]

probably better to do it all in one query rather than chaining queries together, but perhaps something like

SELECT *
FROM  qu_combined_sp  A
WHERE Rank=(SELECT min(Rank) FROM  qu_combined_sp WHERE RGR=A.RGR)

 

[NickOnTheNet]

Hi Nick,

What is the logic we can use to determine a duplicate?

Hi, duplicates are always based on the RGR column.

 

[NickOnTheNet]

probably better to do it all in one query rather than chaining queries together, but perhaps something like

SELECT *
FROM  qu_combined_sp  A
WHERE Rank=(SELECT min(Rank) FROM  qu_combined_sp WHERE RGR=A.RGR)

Perfect!! Thank you!!
Another day where I learned something

 

[CJ_London]

a faster way would be

SELECT A.*
FROM qu_combined_sp A INNER JOIN (SELECT RGR, min(Rank) as mRank FROM qu_combined_sp GROUP BY RGR) B ON A.RGR=B.RGR AND A.Rank=B.mRank