CODEWITHSUNDEEP
mysqlsqlrow-number
Sush
Sush

Reputation: 43

Find All but last duplicate records from MYSQL database

I have a mysql table with following records

-------------------------------
| ID | Name | Age | XXX | YYY |
-------------------------------
| 1  | aa   | 12  | qqq | rr  |
-------------------------------
| 2  | aa   | 12  | ttt | pp  |
-------------------------------
| 3  | bb   | 13  | qhq | rr  |
-------------------------------
| 4  | bb   | 13  | pqq | tr  |
-------------------------------
| 5  | bb   | 13  | ql  | jjn |
-------------------------------

My requirement is to retrieve all duplicate records with respect to first column apart from the last entry. Currently I tried to retrieve duplicates and that is working fine

SELECT Name, Age, XXX, YYY FROM list
INNER JOIN (SELECT Name
           FROM   list
           GROUP  BY Name
           HAVING COUNT(Name) > 1) dup
           ON list.Name = dup.Name;

Output is like

-------------------------------
| ID | Name | Age | XXX | YYY |
-------------------------------
| 1  | aa   | 12  | qqq | rr  |
-------------------------------
| 2  | aa   | 12  | ttt | pp  |
-------------------------------
| 3  | bb   | 13  | qhq | rr  |
-------------------------------
| 4  | bb   | 13  | pqq | tr  |
-------------------------------
| 5  | bb   | 13  | ql  | jjn |
-------------------------------

But I want to remove the last record from the output like

-------------------------------
| ID | Name | Age | XXX | YYY |
-------------------------------
| 1  | aa   | 12  | qqq | rr  |
-------------------------------
| 3  | bb   | 13  | qhq | rr  |
-------------------------------
| 4  | bb   | 13  | pqq | tr  |
-------------------------------

How can I achieve this?

Upvotes: 1

Views: 58

Answers (1)

D-Shih
D-Shih

Reputation: 46219

I would create two new columns in COUNT and Row_number the result because

  1. Get COUNT total number by name column to get the MAX rownumber.
  2. use Row_number by name to get rownumber.
  3. To write a condition exclude the last rownumber by name from total count

If your mysql version support window function you can try this code.

make Row_number and COUNT then remove greater Row_number by Name.

SELECT  *
FROM (
  select *,
         Row_number() over(partition by Name order by ID) rn,
         COUNT(*) over(partition by Name) totle
  from `list` 
) t1
where rn <> totle

sqlfiddle

EDIT

if you have ID auto_increment column and your mysql didn't support window function you can try this query.

SELECT id,Name,Age,XXX,YYY
FROM (
  SELECT *,
         (SELECT COUNT(*) FROM `list` t1 WHERE t.ID >= t1.ID AND t.Name = t1.Name) rn,
         (SELECT COUNT(*) FROM `list` t1 WHERE t.Name = t1.Name) totle
  FROM `list` t
) t1 
where rn <> totle

sqlfiddle

[Results]:

| id | Name | Age | XXX | YYY |
|----|------|-----|-----|-----|
|  1 |   aa |  12 | qqq |  rr |
|  3 |   bb |  13 | qhq |  rr |
|  4 |   bb |  13 | pqq |  tr |

Upvotes: 2

Related Questions