select distinct mobile and second name

Question

i have table like this....................

SNO MOBILE NAME

1 8987654321 RAJUVARDHAN

2 8987654321 PRANAYE KUMAR

3 8987854321 VAMSHIDHAR

4 9014494875 SHASHI

5 9014494875 SUVARNA VARMA

6 9014494875 MEENAREDDY

7 9866029162 JANSIVARMA

8 9866029162 PRAGATHIVARMA

9 9866029162 SAMBA VARMA

I Want output like this...

select distint mobile and second name only

like this

SNO MOBILE NAME

1 8987654321 PRANAYE KUMAR

4 9014494875 SUVARNA VARMA

7 9866029162 PRAGATHIVARMA

Javeed M Shaikh · Accepted Answer

Yes this will work on order by ID or any column you like, without order by you have to make a cursor and put logic yourself which i think if over doing.
the below query should take care of duplicate Mobile or single Mobile
SELECT  MOBILE, NAME  FROM    (          SELECT  MOBILE, NAME, ROW_NUMBER() OVER (PARTITION BY MOBILE ORDER BY NAME) rn          FROM MYTABLE         ) q  WHERE   rn = 2  UNION  SELECT  MOBILE, NAME FROM MYTABLE where MOBILE IN  (  SELECT  MOBILE FROM MYTABLE  MINUS  (  SELECT  MOBILE  FROM    (          SELECT  MOBILE, NAME, ROW_NUMBER() OVER (PARTITION BY MOBILE ORDER BY NAME) rn          FROM MYTABLE          ) q  WHERE   rn = 2  ))  ORDER BY MOBILE

chetan tembhre · Answer

Hi,
Try below query,
with CTE as(select row_number() over(partition by Mobileno order by mobileno) row, mobileno,name from employee)  select * from CTE where row=2 and row not in(1)

Jignesh Trivedi · Answer

hi,
Javeed solution is good.
is there any requirement like if table is containing only single value of mobile number in table than you query must return first value else second value?
in this case above solution is fail to retrieve the record.

Javeed M Shaikh · Answer

try this:
SELECT  MOBILE, NAME  FROM    (          SELECT  MOBILE, NAME, ROW_NUMBER() OVER (PARTITION BY id ORDER BY data) rn          FROM MYTABLE         ) q  WHERE   rn = 2  ORDER BY MOBILE