{"id":136,"date":"2010-02-22T22:06:18","date_gmt":"2010-02-23T02:06:18","guid":{"rendered":"http:\/\/www.peteonsoftware.com\/?p=136"},"modified":"2024-03-01T17:37:36","modified_gmt":"2024-03-01T22:37:36","slug":"row_number-part-two","status":"publish","type":"post","link":"https:\/\/www.peteonsoftware.com\/index.php\/2010\/02\/22\/row_number-part-two\/","title":{"rendered":"Row_Number() Part Two"},"content":{"rendered":"

Last Time<\/a>, 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). <\/p>\n

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.<\/p>\n

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. <\/p>\n

\r\nUSE tempdb\r\nGO\r\n\r\nCREATE TABLE Person (PersonId INT IDENTITY(1,1) PRIMARY KEY, Name VARCHAR(50))\r\nGO\r\n\r\nINSERT Person ([Name])\r\nSELECT 'Bill'\r\nUNION\r\nSELECT 'Bob'\r\nUNION\r\nSELECT 'Carl'\r\nUNION\r\nSELECT 'Sue'\r\nUNION\r\nSELECT 'Cleo'\r\nUNION\r\nSELECT 'Nick'\r\nUNION\r\nSELECT 'Thomas'\r\nUNION\r\nSELECT 'Jeydro'\r\nUNION\r\nSELECT 'Ronald'\r\nUNION\r\nSELECT 'Jamie'\r\nUNION\r\nSELECT 'Colleen'\r\nUNION\r\nSELECT 'Denise'\r\nUNION\r\nSELECT 'Phil'\r\nUNION\r\nSELECT 'Jim'\r\nUNION\r\nSELECT 'Jack'\r\nUNION\r\nSELECT 'Kathleen'\r\nGO\r\n\r\nCREATE TABLE PersonAddress (PersonAddressId INT IDENTITY(1,1) PRIMARY KEY, PersonId INT, [Address] VARCHAR(100))\r\nGO\r\n\r\nINSERT PersonAddress (PersonId, [Address]) \r\nSELECT PersonId, CONVERT(VARCHAR, PersonId * 123) + ' ' + UPPER(REVERSE([Name])) + ' ST.'\r\nFROM Person\r\nORDER BY PersonId DESC\r\nGO\r\n\r\nINSERT PersonAddress (PersonId, [Address]) \r\nSELECT PersonId, CONVERT(VARCHAR, PersonId * 321) + ' SQL DR.'\r\nFROM Person\r\nWHERE PersonId BETWEEN 3 AND 9\r\nGO\r\n\r\nSELECT p.PersonId, p.[Name], pa.[Address]\r\nFROM Person p\r\nINNER JOIN PersonAddress pa\r\nON p.PersonId = pa.PersonId\r\nORDER BY p.PersonId\r\nGO\r\n\r\nDROP TABLE Person \r\nGO\r\nDROP TABLE PersonAddress\r\nGO\r\n<\/pre>\n
Here is a sample of the results:
\n\"One<\/p>\n
Using a similar trick to what we used last time<\/a> 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<\/p>\n
\r\n; WITH Addresses\r\nAS \r\n(\r\n\tSELECT p.PersonId, p.[Name], pa.[Address], ROW_NUMBER() OVER (PARTITION BY p.PersonId ORDER BY p.PersonId ASC) AS RowNum  \r\n\tFROM Person p\r\n\tINNER JOIN PersonAddress pa\r\n\tON p.PersonId = pa.PersonId\r\n)\r\nSELECT PersonId, [Name], [Address], RowNum\r\nFROM Addresses\r\nGO\r\n<\/pre>\n
We get the following results:
\n\"One<\/p>\n
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.<\/p>\n
\r\n; WITH Addresses\r\nAS \r\n(\r\n\tSELECT p.PersonId, p.[Name], pa.[Address], ROW_NUMBER() OVER (PARTITION BY p.PersonId ORDER BY p.PersonId ASC) AS RowNum  \r\n\tFROM Person p\r\n\tINNER JOIN PersonAddress pa\r\n\tON p.PersonId = pa.PersonId\r\n)\r\nSELECT PersonId, [Name], [Address]\r\nFROM Addresses\r\nWHERE RowNum = 1\r\nGO\r\n<\/pre>\n
Our results:
\n\"One<\/p>\n","protected":false},"excerpt":{"rendered":"
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 …<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[15,87],"tags":[89,4],"class_list":["post-136","post","type-post","status-publish","format-standard","hentry","category-code-tips","category-sql","tag-code-tips","tag-sql"],"_links":{"self":[{"href":"https:\/\/www.peteonsoftware.com\/index.php\/wp-json\/wp\/v2\/posts\/136","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.peteonsoftware.com\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.peteonsoftware.com\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.peteonsoftware.com\/index.php\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.peteonsoftware.com\/index.php\/wp-json\/wp\/v2\/comments?post=136"}],"version-history":[{"count":0,"href":"https:\/\/www.peteonsoftware.com\/index.php\/wp-json\/wp\/v2\/posts\/136\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.peteonsoftware.com\/index.php\/wp-json\/wp\/v2\/media?parent=136"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.peteonsoftware.com\/index.php\/wp-json\/wp\/v2\/categories?post=136"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.peteonsoftware.com\/index.php\/wp-json\/wp\/v2\/tags?post=136"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}