CODEWITHSUNDEEP
sqlsql-servert-sqlsubquery
A J
A J

Reputation: 63

columns referencing each other

Let's say there is a table that contains data rows as below.

enter image description here

As you see above, Applicant 79 has a twin 80 and Twin 80 has Applicant 79 as a twin.

Since ID 11 and 12 are really the same information, I am wishing to select a unique twin data as following.

enter image description here

How can I achieve this in SQL Server?

Thank you very much in advance.

Upvotes: 0

Views: 64

Answers (2)

The Impaler
The Impaler

Reputation: 48770

You can simply do:

select * from t where ApplicantId < TwinId

Upvotes: 3

GMB
GMB

Reputation: 222402

One option uses not exists

select t.*
from mytable t
where not exists (
    select 1
    from mytable t1
    where 
        t1.applicantid = t.twinid
        and t1.twinid = t.applicantid
        and t1.applicantid < t.applicantid
)

When mirror records are present, this retains the record with the smallest applicantid.

Upvotes: 2

Related Questions