# 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