CODEWITHSUNDEEP
sqlsql-server-2008t-sql
HelloWorld1
HelloWorld1

Reputation: 14108

Display duplicated data in the list

Goal:
Display the duplicated data, in table testing, that shall be deleted.
However, If there are two duplicated data that is for instance "3412 New York". 1 row only should be displayed in the testing table.

Problem:
Don't know how to do it. Please keep in mind that this table can be a million of row.

All data from the list

table testing
Region          Category    Energy
----------------------------------
1235 Delaware   candy       5
1235 Delaware   candy       5
1235 Delaware   candy       5
1235 Delaware   candy       5
1235 Delaware   candy       5
1235 Delaware   candy       5
1235 Delaware   candy       5
3125 New Jersey drink       4
3125 New Jersey drink       4
3125 New Jersey drink       4
3125 New Jersey drink       4
3125 New Jersey drink       4
3412 New York   food        3
3412 New York   food        3
3412 Washington coke        7
3412 california chips       20
3412 california chips       20
3412 california chips       20
3412 california chips       20





Requested result that should display data in table testing
Table testing
Region          Category    Energy
----------------------------------
1235 Delaware   candy       5
1235 Delaware   candy       5
1235 Delaware   candy       5
1235 Delaware   candy       5
1235 Delaware   candy       5
1235 Delaware   candy       5
3125 New Jersey drink       4
3125 New Jersey drink       4
3125 New Jersey drink       4
3125 New Jersey drink       4
3412 New York   food        3
3412 california chips       20
3412 california chips       20
3412 california chips       20

Upvotes: 0

Views: 49

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1270653

Because you have no primary key, you can just regenerate the table using a group by or distinct:

select distinct Region, Category, Energy
into newt
from t

This would save you from having to identify duplicates and then figure out again how to delete those rows.

Upvotes: 0

gbn
gbn

Reputation: 432511

To see the results

SELECT
    Region, Category, Energy
FROM
    (
    SELECT
       Region, Category, Energy,
       ROW_NUMBER() OVER (PARTITION BY Region, Category, Energy ORDER BY Region) AS rn
    FROm
       MyTable
    ) X
WHERE
   X.rn > 1

Upvotes: 1

Related Questions