CODEWITHSUNDEEP
sqlsql-serversql-server-2008
User5590
User5590

Reputation: 1435

Get Unique data from table

I have one table with some data , which is shown as below.

enter image description here

I want to get unique data from the table and i have tried below code

SELECT
    sa.EMPID,
    sa.EMPNAME,
    sa.DEPTID
FROM dbo.Sample AS sa
LEFT OUTER JOIN dbo.Sample AS se ON sa.EMPID = se.EMPID

but not able to get the result.

I want the query to get below data

enter image description here

Can anyone please help me to solve this query..?

Upvotes: 2

Views: 93

Answers (5)

Al-3sli
Al-3sli

Reputation: 2181

this what you want :

SELECT Min(EMPID) AS EMPID,
EMPNAME,
DEPTID
FROM dbo.Sample
Group By EMPNAME, DEPTID

Upvotes: 0

Devart
Devart

Reputation: 121922

Try this one -

SELECT EMPID, EMPNAME, DEPTID
FROM (
    SELECT
        EMPID,
        EMPNAME,
        DEPTID,
        RowNum = ROW_NUMBER() OVER (PARTITION BY EMPNAME, DEPTID ORDER BY 1/0)
    FROM dbo.[Sample]
) t
WHERE RowNum = 1

Upvotes: 4

Vignesh Kumar A
Vignesh Kumar A

Reputation: 28403

Try this

With tmp AS
FROM (SELECT S.EMPID,S.EMPNAME,S.DEPTID,RowNum = ROW_NUMBER() OVER (PARTITION BY S.EMPNAME, S.DEPTID ORDER BY S.EMPID)
    FROM dbo.[Sample] S LEFT JOIN dbo.[Sample] T ON T.EMPID = S.EMPID)     
SELECT EMPID,EMPNAME,DEPTID FROM tmp WHERE RowNum = 1

Upvotes: 0

Mittal Patel
Mittal Patel

Reputation: 848

try this : 

SELECT EMPNAME,
       DEPTID,
       MIN(EMPID) AS EMPID
       FROM  dbo.Sample
 GROUP BY EMPNAME, DEPTID

Upvotes: 5

Mureinik
Mureinik

Reputation: 311326

You can group by empname and deptid, and select the minimal empid:

SELECT     MIN(sa.EMPID), sa.EMPNAME, sa.DEPTID
FROM       dbo.Sample AS sa LEFT OUTER JOIN
           dbo.Sample AS se ON sa.EMPID = se.EMPID
GROUP BY   sa.EMPNAME, sa.DEPTID

Upvotes: 3

Related Questions