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