Common table expression in SQL

Question

I ve a table named as tbl_Emp:

Now I want to select Unique Emp_Name with EmpID and EmpSalary as follows.I just want to know that what will be the Common table expression in SQL to do this.

Answer 1

This is a simple query using CTE

WITH CTE AS(   
SELECT ROW_NUMBER() OVER(PARTITION BY EmpName ORDER BY EmpID) AS ROWNO,    
                    EmpID,EmpName, EmpSalary FROM tbl_emp
) 
SELECT EmpID, EmpName, EmpSalary  FROM CTE WHERE ROWNO = 1 order by EmpID

If you want Remove the duplicate data use the script as bellow

WITH CTE AS(   
    SELECT ROW_NUMBER() OVER(PARTITION BY EmpName ORDER BY EmpID) AS ROWNO,    
                        EmpID, EmpName, EmpSalary FROM tbl_emp
    ) 
    DELETE FROM CTE WHERE ROWNO > 1

I think the query can help you

Answer 2

I don't think there's a need for a CTE on this. I would JOIN the table with itself. Something like:

SELECT t.* FROM tbl_Emp t
INNER JOIN
(
  select MIN(EmpID) FirstEmp from tbl_Emp group by EmpName
) t2
ON t2.FirstEmp=t.EmpID;

Basically, I'm joining in a second table that has the lowest EmpID field of each distinct EmpName, then selecting all rows that match one of those EmpID fields.

SQL Fiddle

Answer 3

It looks like you are looking for DISTINCT.

something like:

SELECT DISTINCT Emp_Name, EmpID, EmpSalary
FROM tbl_emp;