Using Row_Number to pick the correct entry.

There have been numerous occasions where I have to pick a one of two records out of a given result set, but how I determine which one has some unique rules.  I have two examples of this and I show how I used Row_Number() to solve this.

The first set of data has these sets of rules:

  • For any given ISBN13, give me the 'Acquisition Editor' if they exist and if they do not exist, provide the 'Editor', known as [Role].
  • If there are multiple persons for the chosen [Role], pick the one with the lowest [SortOrder].
  • If [SortOrder] is null, pick the first name alphabetically.

Here is the solution:

select ISBN13,
       DisplayName,
       [Role]
  from (select ISBN13,
               DisplayName,
               [Role],
               ROW_NUMBER() OVER(Partition by ISBN13 order by [Role],isnull(cast(SortOrder as varchar(40)),DisplayName)) as Row
          from dbo.ProductContact
          where C006_Role in ('Acquisition Editor','Editor')) d
where Row=1

The second set of data had the following rules:

  • For a given ISBN13, provide the sum of the  'First Printing', which can be under either PrintNumber '0000' or '0001'. 
  • PrintNumber '0000' is always chosen over '0001' if it exists.

Here is the solution:

select isbn13,
       Printing_Number,
       First_Run
   from (SELECT isbn13,
                Printing_Number,
                Sum(Print_Run) as First_Run,                
                ROW_NUMBER() OVER(PARTITION by isbn13 order by Printing_Number) as Row
           FROM dbo.PPB
          where Printing_Number in ('0000','0001')
          group by isbn13,Printing_Number) d
  where Row=1