select distinct mobile ,firstname

JAYRAM

13y2k

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 first name only

like this

SNO MOBILE NAME

1 8987654321 RAJUVARDHAN

4 9014494875 SHASHI

7 9866029162 JANSIVARMA

4 Replies

Know the answer? Post it — somebody with the same question will find it here.

Jignesh Trivedi13y ago
Accepted answer
hi,
Try following SQL
CREATE Table #test
(
SNO int,
MOBILE varchar(12),
NAME   varchar(100)
)
Insert Into #test values (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')
select A.SNO,A.MOBILE,t.NAME from (
select MIN(SNO) as SNO, MOBILE from #test
group by MOBILE) A
join #test t on t.SNO = A.SNO
hope this will help you.
Naveed Zaman13y ago
very nice and interesting question. it only possible with temporary table need to created using inner join its possible but need to carefully use where statement other wise its will be difficult
JAYRAM13y ago
I HAVE TABLE like this

SNO   MOBILE        NAME
1   9849313820   VAMSHI
2 9849313820 RAJU
3 9849313820 PRANAY
4    9000066621 SUPRAJ
5 9000066621 ARAVIND
6 9866029167   ANR
7 9866029167 SUVARNA

SELECT DISTINCT MOBILE NUMBER AND SELECT NAMES1,NAME2,NAME3......
i wnt select like this....
SNO       MOBILE     NAME 1      NAME 2   NAME 3
1 9849313820     VAMSHI RAJU PRANAY
4   9000066621    SUPRAJ ARAVIND
6 9866029167       ANR SUVARNA
Siva Ganesh Rongala13y ago
I think this is your solution
select
1    as SNO ,       8987654321 as MOBILE,            'RAJUVARDHAN' as NAME INTO #temp UNION ALL SELECT
2    ,       8987654321    ,              'PRANAYE KUMAR' UNION ALL SELECT
3     ,      8987854321   ,               'VAMSHIDHAR' UNION ALL SELECT
4      ,     9014494875   ,           'SHASHI' UNION ALL SELECT
5     ,      9014494875   ,             'SUVARNA VARMA' UNION ALL SELECT
6     ,      9014494875   ,              'MEENAREDDY' UNION ALL SELECT
7     ,      9866029162   ,           'JANSIVARMA' UNION ALL SELECT
8      ,     9866029162   ,             'PRAGATHIVARMA' UNION ALL SELECT
9     ,      9866029162   ,             'SAMBA VARMA'

with x as (
select row_number() OVER (PARTITION BY MOBILE Order BY SNO)RowNum,* FROM #temp)
SELECT SNO,MOBILE,NAME from x where x.RowNum=1