Different ways to write Distinct Query - MSSQL

,

The above code is in General, please map to your original requirement.

--Method 1(a)
select distinct col1, col2 from table1
--Method 1(b)
select distinct col1, col2 from table_2order by col1, col2

--Method 2(a)
select col1, col2 from table1
group by col1, col2 
--Method 2(b)
select col1, col2 from table1
group by col1, col2 
having count(*) = 1
union
select col1, col2 from table1
group by col1, col2 
having count(*) > 1

--Method 3
select col1, col2 from table1
union
select col1, col2 from table1

--Method 4
while @@rowcount > 0
begin
insert into table_3
select col1, max(col2), max(col3) from table1 a 
where a.col1 not in  
( 
select col1 from table2 b  
where a.col1 = b.col1 and a.col2 = b.col2 
and ISNULL(a.col3, '') = ISNULL(b.col3, '') 
) 
and a.col2 is not null and a.col3 is not null 
group by col1 
end

--Method 5
create unique index temp_idxon table1(col1, col2, ..., colN)
with ignore_dup_keyinsert into temp_idx (col1, col2, ..., colN) 
select (col1, col2, ..., colN) from table

--Method 6 (SQL Server 2005 and above)
select col1, col2 from  
( 
SELECT *, row_number() OVER(PARTITION BY col1, col2 ORDER BY (SELECT 1)) rn 
FROM dbo.table1 
) a
where rn = 1

Rate

3.17 (6)

Share

Share

Rate

3.17 (6)