CODEWITHSUNDEEP
sqlsql-serversql-server-2014-express
Sixthsense
Sixthsense

Reputation: 1975

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

Upvotes: 2

Views: 1798

Answers (4)

Daniel Stackenland
Daniel Stackenland

Reputation: 3239

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

Upvotes: 0

Serg
Serg

Reputation: 22811

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)

Upvotes: 0

Prahalad Gaggar
Prahalad Gaggar

Reputation: 11609

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

Upvotes: 1

Giorgos Betsos
Giorgos Betsos

Reputation: 72185

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

Upvotes: 5

Related Questions