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.
CTE (Common Table Expression) syntax can be a little tricky at first, but once you have a little primer, you will love using them as much as I do. Examine the following code below that uses a derived table from which to select. (Please realize that this is a contrived example and you could of course just join the tables in the query directly.)
USE tempdb GO SELECT t.TableName, c.ColumnName FROM (SELECT OBJECT_ID AS Id, [Name] AS TableName FROM sys.tables) t INNER JOIN (SELECT OBJECT_ID AS Id, [Name] AS ColumnName FROM sys.columns) c ON t.Id = c.Id
The query between the parentheses creates a table structure (that I aliased as “c” and “t” for my two) that only lasts for the life of the query. I can then reference those tables in the query in any way that I would a regular table. However, that syntax can make it very difficult to read, especially in long queries with several complicated derived tables.
Sql Server 2005 introduced a new syntax to be used instead called Common Table Expressions (CTEs). Using a CTE, the above query would be written like this
USE tempdb GO WITH Tbls AS ( SELECT OBJECT_ID AS Id, [Name] AS TableName FROM sys.tables ), Clmns AS ( SELECT OBJECT_ID AS Id, [Name] AS ColumnName FROM sys.columns ) SELECT t.TableName, c.ColumnName FROM Tbls t INNER JOIN Clmns c ON t.Id = c.Id
Alternatively, you can also explictly declare you column names in the CTE definition and not have to alias the columns in the query itself.
USE tempdb GO WITH Tbls (Id, TableName) AS ( SELECT OBJECT_ID, [Name] FROM sys.tables ), Clmns (Id, ColumnName) AS ( SELECT OBJECT_ID, [Name] FROM sys.columns ) SELECT t.TableName, c.ColumnName FROM Tbls t INNER JOIN Clmns c ON t.Id = c.Id
One “gotcha” is that the CTE – beginning with the WITH keyword – must be the first statement that is encountered. All previous statements above it must be terminated with a GO or a semicolon. If you ran the following
USE tempdb GO SELECT 'Here Comes the Error' WITH Tbls (Id, TableName) AS ( SELECT OBJECT_ID, [Name] FROM sys.tables ), Clmns (Id, ColumnName) AS ( SELECT OBJECT_ID, [Name] FROM sys.columns ) SELECT t.TableName, c.ColumnName FROM Tbls t INNER JOIN Clmns c ON t.Id = c.Id
You’d get the errors shown below (or something very close)
Msg 102, Level 15, State 1, Line 4 Incorrect syntax near 'Tbls'. Msg 102, Level 15, State 1, Line 8 Incorrect syntax near ','.
Some people even just make a habit of actually prefixing the WITH with a semicolon every time to make sure that everything goes off without a hitch. If I do run this script instead there are no issues.
USE tempdb GO SELECT 'No Problems Here' ;WITH Tbls (Id, TableName) AS ( SELECT OBJECT_ID, [Name] FROM sys.tables ), Clmns (Id, ColumnName) AS ( SELECT OBJECT_ID, [Name] FROM sys.columns ) SELECT t.TableName, c.ColumnName FROM Tbls t INNER JOIN Clmns c ON t.Id = c.Id
This is just a quick primer on CTEs, but I hope that you can find it useful. I really find that it cleans my code up quite a bit.
A few times recently when I’ve been creating a view for full text indexing on Sql Server, I’ve had need to return the results of a one to many relationship in one row. I didn’t need dynamic columns or anything, what would suit me best would be to have all of the values just in one comma separated list. After searching the net, I found an ingenious use of FOR XML PATH to solve this problem.
Here is the standard query to return the list of employees and their sales territories from the Northwind database.
USE Northwind go SELECT e.EmployeeId, e.FirstName, e.LastName, t.TerritoryDescription FROM Employees e INNER JOIN EmployeeTerritories et ON e.EmployeeID = et.EmployeeID INNER JOIN Territories t ON et.TerritoryID = t.TerritoryID
The resulting set looks like the following:
What might be best is some kind of built in function that did the following (kind of like C#’s string.Join()):
USE Northwind go -- This Code is fictitious, CONCATENATE() is not a real aggregate function SELECT e.EmployeeId, e.FirstName, e.LastName, CONCATENATE(t.TerritoryDescription, ',') as TerritoryList FROM Employees e INNER JOIN EmployeeTerritories et ON e.EmployeeID = et.EmployeeID INNER JOIN Territories t ON et.TerritoryID = t.TerritoryID GROUP BY e.EmployeeId, e.FirstName, e.LastName
However, nothing like that was to be found. What I did find was someone who used FOR XML PATH in an interesting way. Normally, FOR XML PATH would return some normal looking xml, however if you pass in the ” as the argument, you just end up with all of the values that would be in an xml string, but with no resulting tag soup.
Running the following code:
USE Northwind
go
SELECT e.EmployeeId, e.FirstName, e.LastName,
STUFF((SELECT ',' + COALESCE(LTRIM(RTRIM(t.TerritoryDescription)), '')
FROM EmployeeTerritories et
INNER JOIN Territories t
on et.TerritoryID = t.TerritoryID
WHERE et.EmployeeID = e.EmployeeId
FOR XML PATH('') ), 1, 1, '') as TerritoryList
FROM Employees e
Gives the following result:
This does do a subselect, but I’ve found that it doesn’t hurt performance too badly. The only other trick here is the STUFF() function, which replaces part of a string with another. In this case, I’m replacing only the first character (the 1, 1) arguments with just an empty string, effectively removing our extra comma.
I hope that someone may have found this useful and if you have any other techniques for solving this kind of problem, I’d love to hear them.
Microsoft Dynamics GP (formerly Great Plains) is a bit of a bear to do integration with. At first, you might think that it should be straightforward. Basically, it is a windows application with a SQL Server backend whose tables (albeit cryptically named) are updated by stored procedures. The problem begins to get deeper when you find out that all of the stored procedures are passed XML documents as parameters.
Microsoft tries to remedy this by offering eConnect (.Net wrapper over COM components that turn objects into the XML files that the procedures need) and the GP Web Services (add a layer). The issue comes, however, when you need some sort of functionality that either doesn’t provide you. Unsurprisingly, you don’t get very far until you exceed the out of the box functionality provided by those products.
The most recent problem that I’ve encountered at work surrounds purchase orders. We create two different kinds of purchase orders. The first is the straight forward purchase order where it is for one or more items and we expect to be invoiced from the vendor for the full amount of the purchase order immediately.
The second kind of purchase order that we create is called a Blanket PO. We create this kind of PO when we contract with a vendor for a rental. We know that a vendor will be billing us X amount of dollars every time period for a certain duration. For instance, if we are renting a widget for $10.00 a month for 6 months, we would create a PO for $60.00 with 6 line items of $10.00 each (each line representing a month). However, we only want to release the first line of the purchase order, since that is all that we are expecting to be invoiced for immediately. In the future, we will release each line as the month that they represent comes up.
We used eConnect to try to automatically create these POs. The problem, however, is that eConnect automatically releases all lines of the blanket PO upon creation. Our second problem is that the update does not expose this property for us to go back and update the lines to unreleased. Since Microsoft encrypts the contents of the stored procedures, we could not inspect or alter what was happening.
After troubleshooting on our end and working with our Microsoft Partner, we eventually had to call Microsoft. The support engineer on the phone confirmed that the procedure in question sets the Release column of the POP10110 table to 1 no matter what the input.
We knew that we could just issue a SQL statement to update the column back to 0, but were unsure what else may need to be affected when that column is updated. GP is notorious for many things needing to be interconnected and we didn’t want to find our POs in some weird state several months down the line. The Microsoft engineers were unsure if anything was affected, so they ran some tests and found that this column operates in isolation and we were free to issue our update statement.
So, after we call eConnect to create the blanket PO, we issue this SQL statement. The POP10110 table holds PO line items (POP10100 holds the PO Header), and in our case, we want to set the Release column all of the lines greater than 1 back to 0 and the Released_Date to ‘1/1/1900′ (the default for “no date given” in GP).
DECLARE @PONumber varchar(10) -- This is set here as an example, but you can put it in a proc -- or however you want to get the value in. SET @PONumber = 'PO-1234567' UPDATE dbo.POP10110 SET Release = 0, Released_Date = '1/1/1900' WHERE PONumber = @PONumber AND LineNumber > 1
Simple answer to a complex problem.
