Sixthsense Sixthsense -4 years ago 95
SQL Question

Distinct In SQL Query resulting duplicate Values

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?

enter image description here

Answer Source

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
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download