Getting distinct values from Resultset

Question

I have the following table Employee and records as follows:

Eid      Ename     Phone
------------------------     
 1         A        043
 1         A        067
 2         B        073
 2         B        072
 3         C        753 
 3         C        464

What I've got so far:

SELECT *
FROM   (SELECT Row_number() OVER (ORDER BY Eid ASC) AS rownum,
               Eid,
               Ename,
               Phone
        FROM   Employee
        WHERE  Eid IN(SELECT DISTINCT(Eid)
                      FROM   Employee
                      GROUP  BY Eid)) AS RESULTSET
WHERE  rownum BETWEEN 0 AND 3

Actually the inner query is returning the distinct results but it is not reflected when I am getting the result in final RESULTSET.

Please advise on how to get the distinct RESULTSET. My output should return like below:

Eid      Ename     Phone
------------------------     
 1         A         043
 2         B         073
 3         C         753

What is the rule for getting `043` rather than `067` as the `Phone` for `Eid`? If it's "because that's the first one", you're going to need to think about your design, because records in a SQL table *don't* have any built-in ordering. — AakashM, Aug 15 '12 at 08:03
Assuming that even the phone numbers are also same, also in the output I need the rownum column as well which I need to get from the resultset. — user1599992, Aug 15 '12 at 08:17

score 2 · Answer 1 · answered Aug 15 '12 at 14:13

If you use the QUALIFY clause you can specify which range of Row_Number values you wish to return. The QUALIFY clause is to window aggregate functions what the HAVING clause is to non-window aggregate functions.

SELECT DISTINCT
       e1.eid
     , e1.ename
     , e1.ephone
--     , ROW_NUMBER() OVER(PARTITION BY e1.eid ORDER BY e1.eid) AS RowNum_
FROM emp e1
QUALIFY ROW_NUMBER() OVER(PARTITION BY e1.eid ORDER BY e1.eid) = 1

If you are including RowNum_ in the result set then you can reference it in the QUALIFY instead of the ROW_NUMBER() window aggregate again.

John Woo · Accepted Answer · 2012-08-16T03:30:50.227

Your query is much more complicated. Try simplifying it by using CROSS APPLY

SELECT DISTINCT a.Eid, a.Ename, c.Phone
FROM   EMPLOYEE a
CROSS APPLY
(
  SELECT  TOP 1 ROW_NUMBER() OVER (PARTITION by Ename ORDER BY Eid ASC) AS RowNo,
          b.Eid, b.Phone
  FROM    EMPLOYEE b
  WHERE   a.EID = b.eid 
  ORDER BY RowNo ASC                         -- you can change this to DESC also
) c

The reason why I added DISTINCT is because you are joining the table with itself.

Another simplier version

SELECT DISTINCT a.Eid, a.Ename, c.Phone
FROM   EMPLOYEE a
CROSS APPLY
(
  SELECT  TOP 1 b.Eid, b.Phone
  FROM    EMPLOYEE b
  WHERE   a.EID = b.eid 
) c

SQLFiddle Demo

score 1 · Answer 3 · edited Aug 16 '12 at 03:11

1

Ahhm Im not sure how you want this query but somehow I based this in your expected output

Code:

select    Eid,Ename,Phone 
from      (select ROW_NUMBER() OVER (ORDER BY Eid ASC) AS rownum,
                  Eid,
                  Ename,
                  Phone 
           from Employee 
           where Eid in (select distinct(Eid) 
                         from Employee  )
         ) AS RESULTSET
Where rownum %2<>0

SQLFiddle Demo

edited Aug 16 '12 at 03:11

John Woo

238,432
61
456
464

answered Aug 15 '12 at 08:25

Prince Jea

4,960
6
24
46

Table is - Eid Ename Phone ------------------------ 1 A 043 1 A 043 2 B 073 2 B 073 3 C 753 3 C 753 and the expected output should be - rownum Eid Ename Phone ------------------------ 1 1 A 043 2 1 B 073 3 1 C 753 – user1599992 Aug 15 '12 at 08:35

score 0 · Answer 4 · answered Aug 15 '12 at 08:23

Try this

 SELECT * 
FROM   (SELECT Row_number() OVER (partition by ename ORDER BY Eid ASC) AS rownum, 
               Eid, 
               Ename, 
               Phone 
        FROM   Employeee
        WHERE  Eid IN(SELECT DISTINCT(Eid) 
                      FROM   Employeee
                      GROUP  BY Eid)) AS RESULTSET 
WHERE  rownum =1

score 0 · Answer 5 · answered Aug 15 '12 at 08:33

0

Try this:

 select Eid,Ename,Phone from 
 (select *,ROW_NUMBER() over(partition by Ename order by eid) as rn from emp) a
 where rn=1

answered Aug 15 '12 at 08:33

AnandPhadke

12,218
5
23
33

Getting distinct values from Resultset

5 Answers5

SQLFiddle Demo

SQLFiddle Demo