Selecting partially duplicate records

Question

In a MySQL database table, among other records, there is a number of pairs differing by the value of just one column, like:

| FirstName|LastName |Number|
| -------- | ------- |------|
| John     | Smith   |15    |
| John     | Smith   |32    |
....
| Sarah    | Baker   |8     |
| Sarah    | Baker   |14    |
....

I need a query to select only the records having greater values of the Number field, i.e.

| John     | Smith   |32    |
| Sarah    | Baker   |14    |

Non-duplicate records, i.e. when there is only one record per person, should not be selected

This is not "Get records with max value", but it's about "Getting records with values greater than min value " — Luuk, May 16 '23 at 16:13

score 0 · Answer 1 · answered May 16 '23 at 16:05

You can just use window functions:

select firstname, lastname, num
from (
    select t.*, row_number() over(partition by firstname, lastname order by num) rn
) t
where rn > 1

When a first/last name tuple has multiple rows, row_number() assigns rank 1 to the row that has the smallest num, and then increments accordingly. We can use this information to filter out the "first" row and show only the duplicate. Note that there may be more than one duplicate row per tuple, in which case the query returns them all.

SelVazi · Answer 2 · 2023-05-16T16:33:13.830

0

You can use GROUP BY, the aggregate function MAX(), AND HAVING :

SELECT FirstName, LastName, MAX(number) as number
FROM mytable
GROUP BY FirstName, LastName
having count(1) > 1

edited May 16 '23 at 16:33

answered May 16 '23 at 16:06

SelVazi

10,028
2
13
29

1

You forgot to select: `MIN(number)`? – Luuk May 16 '23 at 16:09
1

sorry, i mis-read ,.... you are right with the MAX... " only the records having greater values of " – Luuk May 16 '23 at 16:11

score 0 · Answer 3 · edited May 20 '23 at 00:10

0

I eventually solved it like this:

select mt.*
from  mytable mt
join
(select FirstName, LastName, max(Number)
from mytable
group by FirstName, LastName
having count(*)>1) t on (t.FirstName = mt.FirstName and t.LastName = mt.LastName)

For some reason window functions didn't work on our server.

edited May 20 '23 at 00:10

Federico Fusco

545
1
5
18

answered May 17 '23 at 20:48

Dmytro

9
2

This gets all duplicates, but you asked for just the rows with the higher number values. Either your answer is wrong, or your question is wrong. Please correct one or the other. – MatBailie May 20 '23 at 00:25
Window functions were introduced to mysql in v8. Your question should specify which version of MySQL you're using. – MatBailie May 20 '23 at 00:26
Nope, this exactly returns the rows with the max number values. – Dmytro May 21 '23 at 20:52

Selecting partially duplicate records

3 Answers3