none
Query assistance

    Question

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

     

    Friday, October 18, 2013 5:52 PM

Answers

  • 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

    • Marked as answer by joemac130 Friday, October 18, 2013 8:48 PM
    Friday, October 18, 2013 6:13 PM

All replies

  • 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

    • Marked as answer by joemac130 Friday, October 18, 2013 8:48 PM
    Friday, October 18, 2013 6:13 PM
  • 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

    Friday, October 18, 2013 8:32 PM
  • 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!

    Friday, October 18, 2013 8:47 PM