Fetching records one with Null values another with proper value.

                  Table: EMP

     

Empno	Ename
100	Jack
100
101	Neha
102	June
103	Miller
104
105	Pam
105

Suppose we have the above records in the table, which have duplicate Empno , one with Ename and another with Null. For example Empno 100 has two value one is Jack another is NULL, same case with Empno 105 ( Empno 104 which has only one value, that is Null).

Scenario: :- Fetch the records from the above table and result should be like below Table.

Empno	Ename
100	Jack
101	Neha
102	June
103	Miller
104
105	Pam

Query:  

SELECT

CCC.EMPNO

,CASE WHEN CCC.ENAME=999 THEN NULL ELSE CCC.ENAME END AS ENAME

FROM

(

SELECT

BBB.EMPNO

,BBB.ENAME

,DENSE_RANK() OVER

    (PARTITION BY BBB.EMPNO ORDER BY BBB.ENAME DESC) AS Rank

FROM

(

SELECT

AAA.EMPNO

,CASE WHEN AAA.ENAME IS NULL THEN 999 ELSE AAA.ENAME END AS ENAME

FROM

(

SELECT

distinct EMP.EMPNO,ENAME FROM EMP

) AS AAA

) AS BBB

) AS CCC

WHERE CCC.RANK = 1 ;