Question
joemac130 on Fri, 18 Oct 2013 17:52:08
Looking for assistance with a select statement - I need to be able to select the FIRST Row of a collection when the collection contains Multiple rows with a common field AccountNumber
I have a table we'll call Customer
Within the table I have fields PKEY, AccountNumber, FullName, FName, LName
Where the PKEY was set based on a unique combinations of the four fields - I cannot use a DISTINCT qualifier due to the construction of the PKEY
The table has multiple records for the AccountNumber
|
PK1 |
Account12345 |
Jane Doe |
Jane |
Doe |
|
PK2 |
Account12345 |
John Doe |
John |
Doe |
|
PK3 |
Account12345 |
Junior Doe |
Junior |
Doe |
The Select statement that I have as a baseline is
select a.[AccountNumber],a.[FullName],a.[FName],a.[LName]
from [dbo].[Customer] a
where exists
(select top 1 aa.[AccountNumber] from [dbo].[Customer] aa
where aa.[AccountNumber]= a.[AccountNumber])
Replies
Visakh16 on Fri, 18 Oct 2013 18:13:51
This?
select a.[AccountNumber],a.[FullName],a.[FName],a.[LName] from ( select [AccountNumber],[FullName],[FName],[LName], ROW_NUMBER() OVER (PARTITION BY [AccountNumber] ORDER BY PK ASC) AS Seq from [dbo].[Customer] )a where Seq=1
joemac130 on Fri, 18 Oct 2013 20:32:42
Hello Visakh16 -
At first when I looked I thought it was working, however, when I add an Order By qualifier there still exists duplicate rows for the same [AccountNumber] in the collection
joemac130 on Fri, 18 Oct 2013 20:47:52
Hello Visakh16 -
I think that I have been steering at the data way TOO long... Please accept my apologies as it does appear that your statement is producing the expected results...
Thank you for your assistance!