How to get distinct records without using distinct keyword

 In this article we are going to see how to retrieve unique records from the data table without using the distinct keyword in microsoft sql server.

We can get the unique records from table without using distinct keyword by the following ways:-

• By Group by
• By Union 
• By Intersection
• By CTE and row_number() function  

Now create sample data table in sql server.

Retrieve the data

  

SELECT * from tblDistinct 

Now get distinct records by using the GROUP BY 

select _value from tblDistinct group by _value

Now get unique records by using the UNION

select _value from tblDistinct

union

select _value from tblDistinct

Now get unique records by using the INTERSECT

select _value from tblDistinct

intersect

select _value from tblDistinct

Now get unique records by using the CTE

with cte (_value, _count)

as

(select _value , ROW_NUMBER() over (partition by _value order by _value ) as _count from tblDistinct)

select * from cte where _count=1;