SQL PRACTICE: SQL SERVER - Unique Records

 Q2: How to select unique values in SQL?

N.B: I am using the following:

Database: MSSQL

SQL Dialect: T-SQL 

Below is an employee table given. It has 3 fields and 7 records.

                                                      Table1: Employee Table

To find unique values of the table, I am going to use three different ways: 

1. Using distinct clause

select distinct EmpId, Name, Salary from employee;

Table2: Distinct Clause Method

2. Using Group by (Without using distinct keyword)

select EmpId, Name, Salary from employee

group by EmpId, Name, Salary;

Table3. Group By Method

3. Using Row_Number Function

select Empid, Name, Salary, ROW_NUMBER() over(partition by empid, Name, Salary order by empid) as Row_Num

from employee;

Result:

Table4. Row_Number Function

Row_number is used to give a unique number for each row as you can see from table 4. Now, my task is  to select row_num = 1. To achieve this, I can  use a common table expression (CTE).

With cte as (select Empid,

Name,

Salary,

ROW_NUMBER() over(partition by empid, Name, 

Salary order by empid) as Row_Num

from employee)

select Empid,

Name,

Salary

from cte

where Row_Num = 1;

Final Result:

                                                    Table5. Row_Number Window Function