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 · 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 mobilenoname from employee) select * from CTE where row=2 and row not in(1)

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

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.