Link to home
Start Free TrialLog in
Avatar of morinia
moriniaFlag for United States of America

asked on

Selecting only one record per account number from each record.when multiple records exist

Experts,

I have a file that has multiple occurrences for the same account number.  The date is the same.  The file has multiple fields on it. I want only one record for each account.

The query below works for one column but my file has many columns.

How can I get just one record in each group?


SELECT  pat_acct_no
      From Test1 S1
       where pat_acct_no IN
      (Select TOP 1 pat_acct_no
        from Test1  S2
        where s1.pat_acct_no = S2.pat_acct_no
        Group  by  pat_acct_no
    )
    Group by pat_acct_no
    Order by pat_acct_no


Avatar of als315
als315
Flag of Russian Federation image

Could you upload sample DB with some dummy data? You should get any unique field in nested query and join it to main table
ASKER CERTIFIED SOLUTION
Avatar of Mark Edwards
Mark Edwards
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial