CODEWITHSUNDEEP
sqlazure-sql-databaseazure-data-factory
Kunal Sinha
Kunal Sinha

Reputation: 77

select all columns from distinct combination of three column

I have to insert record to another table based on the distinct combination of three column. Table A has 50 columns, in that i have to take distinct value of three column and insert all the column values to another table. how i can do that ? I tried doing below but got error .

select * from engagementmasterstaging 
where EngagementId 
in (select distinct EngagementId,ServiceLine,SubServiceLine 
from engagementmasterstaging)

Upvotes: 0

Views: 120

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270633

If you want all columns, use window functions:

select . . .    -- list the columns here
from (select ems.*,
             row_number() over (partition by EngagementId, ServiceLine, SubServiceLine order by EngagementId) as seqnum
      from engagementmasterstaging ems
     ) ems
where seqnum = 1;

If you have a primary key in the staging table, you can use that and still use select *:

select ems.*
from engagementmasterstaging ems
where ems.pk = (select min(ems2.pk)
                from engagementmasterstaging ems2
                where ems2.EngagementId = ems.EngagementId and
                      ems2.ServiceLine = ems.ServiceLine and
                      ems2.SubServiceLine = ems.SubServiceLine
               );

Upvotes: 1

Related Questions