A huge part of database related work is to make sure that the data is consistent. In real world data is never ideal, and whenever you need using data from existing data sources, you have to understand what is right and what is wrong there, and know how to circumvent data quality issues. Two most frequent data integrity issues in relational databases are missing date and duplicate data. A record/document is missing if it was not written to the database by an application, or was mistakenly deleted. A record/document is duplicated if it was recorded more than once.
Why does application write same record more than once? A user or an upstream code could send same document twice, and an application doesn’t handle this case. Or a user could send incorrect record the first time, and a corrected one later: an application could be designed to save all records instead of modifying existing ones.
If you are working on an application which reads this data for reporting or for transaction processing purposes, it is good to start from testing if there are any duplicates in a data source. Let’s say, you have an SQL Server table:
CREATE TABLE dbo.Payment ( PaymentID INT IDENTITY(1,1) NOT NULL PRIMARY KEY CLUSTERED, TransactionID VARCHAR(32) NOT NULL, ... )
PaymentID is an identity column and a primary key. TransactionID is an external key, controlled by a payment processor. If there is no unique constraint on TransactionID field, it is possible to have duplicate payment records. You can find whether there are duplicate TransactionID in this table (all queries in this post are for SQL Server 2008 or later, though other database servers may support them too):
SELECT TransactionID, COUNT(*) FROM Payment GROUP BY TransactionID HAVING COUNT(*) > 1
This query returns all TransactionID used in more than one row (GROUP BY groups by TransactionID, and HAVING filters based on row count). If there any, then you can get into details: eyeball these duplicate records to understand if they are full duplicates, why they could be inserted twice, and what is the best strategy for deduplication. This query returns all rows with duplicate TransactionID:
SELECT * FROM Payment WHERE TransactionID IN (SELECT TransactionID FROM Payment GROUP BY TransactionID HAVING COUNT(*) > 1) ORDER BY TransactionID, PaymentID
If after investigation you decided that you need to query only the latest row (based on PaymentID) for each TransactionID then you can use CTE (common table expression) with ROW_NUMBER to do it:
;WITH dedupPayments AS ( SELECT PaymentID, ROW_NUMBER() OVER(PARTITION BY TransactionID ORDER BY PaymentID DESC) rowNumber FROM Payment ) SELECT * FROM Payment p INNER JOIN dedupPayments d ON d.PaymentID = p.PaymentID AND d.rowNumber = 1
Here CTE returns all rows, adding row number partitioned by TransactionID and sorted by PaymentID. Partitioning is to counting rows separately for each TransactionID. As we need only the last row for each TransactionID, sorting is set to produce rowNumber = 1 for the last row of each partition. Then we query data from Payment table, join with CTE and get only rows with rowNumber=1.
In some cases, CTE can be replaced with a temporary table for performance reasons.