SQL query to find the greater duplicate row

Question

I've a table in a DB that looks like this:

TABLE `partecipanti` (
  `ID` int(11) NOT NULL,
  `Name` varchar(30) DEFAULT NULL,
  `Surname` varchar(30) DEFAULT NULL,
  `Score2` int(11) DEFAULT NULL,
  `Zero1` int(11) DEFAULT '0',
  `Zero2` int(11) DEFAULT '0',
  `Score1` int(10) NOT NULL DEFAULT '0'
)

And a query that looks like this:

SELECT *,Score1 + Score2 as Total FROM partecipanti
ORDER BY Total DESC,Score2 DESC,Zero2 DESC,Score1 DESC, Zero1 DESC;

Now the thing I'd like to do is : when I've a duplicate record (same name and same surname while the other data including ID are differents) pick the row that has the higher score which is stored in the field Total

I was thinking about a nested query,in the first query I order the data and I group them with GROUP BY, then I select the higher element. Could anyone help me please? Thanks.

EDIT:

If I add the DISTINCT statement the query seems to work , is it ok? Thanks.

Use my updated answer. I had a typo in the first answer – Mojtaba Aug 09 '16 at 18:54 — Mojtaba, Aug 09 '16 at 18:54

score 0 · Answer 1 · answered Aug 09 '16 at 18:58

0

You can try to run the following query and find the duplicates with max(id).

SELECT Score1, Score2, COUNT(*), Max(ID) AS dupes 
FROM participanti 
GROUP BY Total 
HAVING (COUNT(*) > 1)

answered Aug 09 '16 at 18:58

R.K123

159
2
9

No , because I don't know if the higher Total score of the duplicate row of the table has also the higher ID. – Luigi Cerone Aug 09 '16 at 19:18

score 0 · Answer 2 · answered Aug 09 '16 at 19:08

Given score2 can be null, try this:

select 
    p1.* 
from 
    partecipanti p1
    join ( select name, surname, max(score1 + coalesce(score2, 0) ) totalScore from partecipanti group by name, surname) p2 on
      p1.Name = p2.Name and
      p1.Surname = p2.Surname and
      p1.score1 + coalesce(p1.score2, 0) = p2.totalScore

SQL query to find the greater duplicate row

2 Answers2