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,
  from (select ISBN13,
               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,
   from (SELECT isbn13,
                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

I'm presenting at 24 Hours of Pass!!

Woohoo!  I have been selected to present my SSIS Tips & Tricks presentation at this year's 24 Hours of Pass.  I am very excited and honored to have been  selected to be one of 24 presenters.  I have decided to include new material in this presentation; Named Pipes.  If that piqued your interest, then please register for my presentation and any of the other fine presentations available.  As a matter of fact, take the day off and register for all 24!  Just click on the link to the left under Contributions or right here to register.