Question 169735 in Mysql Question posted in
The official MySQL documentation can be found here.

Can MySQL select partially duplicate records?

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

Tags:

2

Answers


  1. 0

    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.

    Login or Signup to reply.
  2. 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
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search