Distinct In SQL Query resulting duplicate Values

Question

In my database there is multiple entries from same employee so I would like to get the Latest entry by way of identifying the auto number field. But using below sql code with distinct in eno is resulting duplicate entries.

SELECT distinct(eNo), auto_num from mc_employee 
order by auto_num

Any clues about what I am doing wrong?

Answer 1

DISTINCT is not function applied on specific fields of the SELECT clause. It is rather applied to all the fields present in the SELECT clause.

If you want to get the latest record then you can use ROW_NUMBER:

SELECT eNo, auto_num
FROM (
   SELECT eNo, auto_num,
          ROW_NUMBER() OVER (PARTITION BY eNo ORDER BY auto_num DESC) AS rn 
   from mc_employee ) AS t
WHERE t.rn = 1
ORDER BY auto_num

Answer 2

Try below query:

SELECT eNo,auto_num 
FROM
( SELECT eNo, auto_num, ROW_NUMBER() OVER(PARTITION BY eNo ORDER BY auto_num DESC) as RN
  FROM mc_employee
)T
WHERE RN=1

Answer 3

The simplest way is TOP(1) WITH TIES ... ORDER BY ROW_NUMBER() OVER ()

SELECT TOP(1) WITH TIES eNo, auto_num
FROM mc_employee
ORDER BY ROW_NUMBER() OVER (PARTITION BY eNo ORDER BY auto_num DESC)

Answer 4

Here is an sub-select solution also:

SELECT distinct(dist.eNo), (select max(auto_num) from mc_employee i where i.eNo=dist.eNo) auto_num
from mc_employee dist
order by auto_num