9

How to write an SQL query to identify duplicate values ​​in a specific field?

 3 years ago
source link: https://www.codesd.com/item/how-to-write-an-sql-query-to-identify-duplicate-values-in-a-specific-field.html
Go to the source link to view the article. You can view the picture content, updated content and better typesetting reading experience. If the link is broken, please click the button below to view the snapshot at that time.
neoserver,ios ssh client

How to write an SQL query to identify duplicate values ​​in a specific field?

advertisements

This is the table I'm working with:

QZU7x.png

I would like to identify only the ReviewIDs that have duplicate deduction IDs for different parameters.

For example, in the image above, ReviewID 114 has two different parameter IDs, but both records have the same deduction ID.

For my purposes, this record (ReviewID 114) has an error. There should not be two or more unique parameter IDs that have the same deduction ID for a single ReviewID.

I would like write a query to identify these types of records, but my SQL skills aren't there yet. Help?

Thanks!

Update 1: I'm using TSQL (SQL Server 2008) if that helps
Update 2: The output that I'm looking for would be the same as the image above, minus any records that do not match the criteria I've described.

Cheers!

SELECT * FROM table t1 INNER JOIN (
    SELECT review_id, deduction_id FROM table
    GROUP BY review_id, deduction_id
    HAVING COUNT(parameter_id) > 1
) t2 ON t1.review_id = t2.review_id AND t1.deduction_id = t2.deduction_id;

http://www.sqlfiddle.com/#!3/d858f/3

If it is possible to have exact duplicates and that is ok, you can modify the HAVING clause to COUNT(DISTINCT parameter_id).


report

Recommend

About Joyk


Aggregate valuable and interesting links.
Joyk means Joy of geeK