CODEWITHSUNDEEP
sql-servert-sqljoin
ragu
ragu

Reputation: 663

SQL Server : join with top record selection

Clientcode  Emailaddress    Accountcode clientname      phoneno
----------------------------------------------------------------
AAA         [email protected]        100     Berjeya         90909090
AAA         [email protected]       100     Berjeya         90909090
AAABBB      [email protected]      200     Berjeya sooo    3222
CCCC        [email protected]        200     Berjeya klkl    123
dddd        [email protected]    33300     Berjeya penn    33333

This is the data in my table, I need to remove any one of the email address with same client code and account code. For example the email address [email protected] and [email protected] have the same client code and account code, but email address is different; I need to show only one of the email addresses with all records. Please suggest the suitable query for this.

Upvotes: 0

Views: 78

Answers (1)

Kannan Kandasamy
Kannan Kandasamy

Reputation: 13959

you can use top 1 with ties as below:

Select top (1) with ties * from yourtable
   order by row_number() over(partition by ClientCode,AccountCode order by EmailAddress)

with subquery you can do like below

Select * from (
    Select *, RowN = Row_Number() over(partition by ClientCode, AccountCode order by EmailAddress) from yourtable
 ) a where a.RowN = 1

Upvotes: 3

Related Questions