CODEWITHSUNDEEP
sqlsql-serversql-server-2008
IUnknown
IUnknown

Reputation: 9809

Get list of unique records

I have the following table which lists the employees and their corresponding managers:

id | employeeid | managerid
1  | 34256      | 12789
2  | 21222      | 34256
3  | 12435      | 34256
.....
.....

What is the recommended way to list out all distinct employees(id) in a single list.
Note that all managers may not be listed under the employeeid column (as he may not have a manager in turn).

Upvotes: 0

Views: 53

Answers (3)

Milen
Milen

Reputation: 8867

If I understand this correctly: This will unite all distict Employee IDs avoiding duplicates from between the two column (UNION)

SELECT employeeid  AS Employee
FROM tableA
UNION
SELECT managerid AS Employee
FROM tableA

Upvotes: 2

mohan111
mohan111

Reputation: 8865

SELECT id, employeeid, managerid
FROM
(SELECT yourtablename.*,
ROW_NUMBER() OVER (PARTITION BY managerid ORDER BY employeeid DESC) AS RN
FROM yourtablename) AS t
WHERE RN = 1
ORDER BY ID

Upvotes: 0

Laurent S.
Laurent S.

Reputation: 6946

This should d it : 

SELECT DISTINCT employeeid FROM yourtablename

But seriously, by googling the keyword "distinct" you could have found out very easily yourself ! Or did I miss something out ?

Upvotes: 0

Related Questions