Last Time, we looked at ROW_NUMBER() and saw a little of how it works and how we could use it to remove duplicates. Another one of my favorite uses for ROW_NUMBER() is to get only one row from a one-to-many join. I’ve had situations at work where I need to return one row per Person, but each person had several addresses, several injury codes, and several contacts (people working their case).

In many of those cases, there is nothing to indicate which one of those rows should receive precidence over the others. One might just select from those tables and then take just the MIN() of the primary key, grouping by all other fields, but that makes for very messy and hard to maintain code in my opinion. ROW_NUMBER() gives us a much easier way to handle this problem.

Examine this sql script that gives us a Person Table and a PersonAddress table. One person can have many addresses, but there is no way in the schema to give precidence to one of the addresses over any of the others.

USE tempdb
GO

CREATE TABLE Person (PersonId INT IDENTITY(1,1) PRIMARY KEY, Name VARCHAR(50))
GO

INSERT Person ([Name])
SELECT 'Bill'
UNION
SELECT 'Bob'
UNION
SELECT 'Carl'
UNION
SELECT 'Sue'
UNION
SELECT 'Cleo'
UNION
SELECT 'Nick'
UNION
SELECT 'Thomas'
UNION
SELECT 'Jeydro'
UNION
SELECT 'Ronald'
UNION
SELECT 'Jamie'
UNION
SELECT 'Colleen'
UNION
SELECT 'Denise'
UNION
SELECT 'Phil'
UNION
SELECT 'Jim'
UNION
SELECT 'Jack'
UNION
SELECT 'Kathleen'
GO

CREATE TABLE PersonAddress (PersonAddressId INT IDENTITY(1,1) PRIMARY KEY, PersonId INT, [Address] VARCHAR(100))
GO

INSERT PersonAddress (PersonId, [Address]) 
SELECT PersonId, CONVERT(VARCHAR, PersonId * 123) + ' ' + UPPER(REVERSE([Name])) + ' ST.'
FROM Person
ORDER BY PersonId DESC
GO

INSERT PersonAddress (PersonId, [Address]) 
SELECT PersonId, CONVERT(VARCHAR, PersonId * 321) + ' SQL DR.'
FROM Person
WHERE PersonId BETWEEN 3 AND 9
GO

SELECT p.PersonId, p.[Name], pa.[Address]
FROM Person p
INNER JOIN PersonAddress pa
ON p.PersonId = pa.PersonId
ORDER BY p.PersonId
GO

DROP TABLE Person 
GO
DROP TABLE PersonAddress
GO

Here is a sample of the results:

Using a similar trick to what we used last time to find unique records, we can alter our select statement so that we only return one address per person. If we change our select statement to this

; WITH Addresses
AS 
(
	SELECT p.PersonId, p.[Name], pa.[Address], ROW_NUMBER() OVER (PARTITION BY p.PersonId ORDER BY p.PersonId ASC) AS RowNum  
	FROM Person p
	INNER JOIN PersonAddress pa
	ON p.PersonId = pa.PersonId
)
SELECT PersonId, [Name], [Address], RowNum
FROM Addresses
GO

We get the following results:

You can see now that all we have to do is limit the result where the RowNum is equal to one and we have something that we can use (the CTE) in further joins or in reports to ensure that we don’t introduce duplicates to our query.

; WITH Addresses
AS 
(
	SELECT p.PersonId, p.[Name], pa.[Address], ROW_NUMBER() OVER (PARTITION BY p.PersonId ORDER BY p.PersonId ASC) AS RowNum  
	FROM Person p
	INNER JOIN PersonAddress pa
	ON p.PersonId = pa.PersonId
)
SELECT PersonId, [Name], [Address]
FROM Addresses
WHERE RowNum = 1
GO

Our results:

Sometimes when you are performing queries, you have data that does not have a primary key and contains duplicate records. If it had had a primary key, you could group on the columns that constitute a duplicate and just keep the MIN or MAX of the primary key, deleting the others. However, lacking that primary key, you have no way of uniquely identifying a row. Additionally, UNIQUEIDENTIFIER types don’t work with MAX and MIN, so you’d have a hard time picking a row in that case as well.

One option is of course to add an identity column to the table and do the operation that I described. However, sometimes you don’t have the appropriate permissions or just aren’t allowed to modify the table to do that. You can use the ROW_NUMBER() function to assign a unique row number to each row by partition. I’ll talk a little more about partition later, but when not explicitly specified in the query, the partition refers to the entire table.

Examine the script below. (All Sql Code contained here will only work on Sql Server 2005 and later)

USE tempdb
GO

CREATE TABLE HasDupes (ProductName VARCHAR(50), UpcCode VARCHAR(10))
GO

INSERT HasDupes (ProductName, UpcCode)
SELECT 'Diapers', '0123456780'
UNION ALL
SELECT 'Diapers', '0123456780'
UNION ALL
SELECT 'Diapers', '0123456780'
UNION ALL
SELECT 'Diapers', '0123456780'
UNION ALL
SELECT 'Formula', '0987654320'
UNION ALL
SELECT 'Formula', '0987654320'
UNION ALL
SELECT 'Formula', '0987654320'
UNION ALL
SELECT 'Diet Coke', '0564534230'
UNION ALL
SELECT 'Chair', '0872372530'
GO

SELECT *
FROM HasDupes
GO

DROP TABLE HasDupes
GO

You get the results shown here:

If we want to add a rownumber to the table, we would alter the part of the query that is

SELECT *
FROM HasDupes
GO

to say this

SELECT ProductName, UpcCode, ROW_NUMBER() OVER (ORDER BY ProductName ASC) AS RowNum
FROM HasDupes
GO

and get these results

That helps some, but wouldn’t it be better if we could get a row number assigned within groups? That is what the PARTITION BY clause does. Change the SELECT sql again so that it now looks like this (note: you can PARTITION BY and ORDER BY any columns you want, and they can be the same or different from each other)

SELECT ProductName, UpcCode, ROW_NUMBER() OVER (PARTITION BY UpcCode ORDER BY ProductName ASC) AS RowNum
FROM HasDupes
GO

Now, we get this

Okay, now all we have to do is keep the records that get assigned a one, forsaking all others. Change that same section of code again to read like this and then run the script. Note, I am using the CTE syntax here.

WITH ProductDupes
AS
(
	SELECT ProductName, UpcCode, ROW_NUMBER() OVER (PARTITION BY UpcCode ORDER BY ProductName ASC) AS RowNum
	FROM HasDupes
)

DELETE FROM ProductDupes WHERE RowNum > 1
GO

SELECT *
FROM HasDupes
GO

Now we have a table with no duplicates.

That’s it, that is all there is to it. In my next blog, I will cover how we can use this trick in joins to ensure that we only get a 1 to 1 join, even when the data is 1 to Many.