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])

 


Sponsored



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!