Remove duplicates based on a criteria (1 Viewer)

NickOnTheNet

New member
Local time
Today, 16:34
Joined
Sep 2, 2020
Messages
4
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

query.JPG


Thanks for your time!!

Regards... Nick
 

theDBguy

I’m here to help
Staff member
Local time
Today, 08:34
Joined
Oct 29, 2018
Messages
21,359
Hi Nick,

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

CJ_London

Super Moderator
Staff member
Local time
Today, 15:34
Joined
Feb 19, 2013
Messages
16,555
probably better to do it all in one query rather than chaining queries together, but perhaps something like

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

NickOnTheNet

New member
Local time
Today, 16:34
Joined
Sep 2, 2020
Messages
4
probably better to do it all in one query rather than chaining queries together, but perhaps something like

Code:
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 (y) (y)
 

CJ_London

Super Moderator
Staff member
Local time
Today, 15:34
Joined
Feb 19, 2013
Messages
16,555
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
 

Users who are viewing this thread

Top Bottom