HOW TO SELECT DISTINCT MOBILE AND FIRST NAME ONLY

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

like this

SNO MOBILE NAME

1 8987654321 RAJUVARDHAN

4 9014494875 SHASHI

7 9866029162 JANSIVARMA

Jignesh Trivedi · Answer

hi you can also try with 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.SNOA.MOBILEt.NAME from ( select MIN(SNO) as SNO MOBILE from #test group by MOBILE) A join #test t on t.SNO = A.SNO also it can be possible with CTE. hope this will help you.

Ravi Shekhar · Answer

nice query jignesh sir. Thanks a lot

Hemant Srivastava · Answer

you can concatenate the columns and then select the distinct one... SELECT DISTINCT (MOBIL + NAME) FROM YOUR_TABLE

Jignesh Trivedi · Answer

hi you can get out put using Rollup and Cube Please refer below example CREATE Table #test ( Id int area varchar(100) City varchar(100) ) Insert Into #test values (1'Noida''Delhi') (2'Uttam Nagar''Delhi') (3'JanakPuri''Delhi') (4'Panvel''Mumbai') (5'Kharghar''Mumbai') (6'Nerul''Mumbai') (7'CP''Delhi') (9'Lucknow''UttarPradesh') (10'Panipat''Haryana') SELECT City CASE WHEN GROUPING(area) = 1 THEN 'All Area ' + City ELSE area END area FROM #test GROUP BY areaCity with cube hope this will help you.

Ravi Shekhar · Answer

\sir i have a table like... Noida Delhi 1 Uttam Nagar Delhi 2 JanakPuri Delhi 3 Panvel Mumbai 4 Kharghar Mumbai 5 Nerul Mumbai 6 Noida Delhi 7 CP Delhi 8 Lucknow UttarPradesh 9 Panipat Haryana 10 I need a output like ... Delhi then city under delhi Mumbai then city under Mumbai Help me.

Jignesh Trivedi · Answer

Thanks Ravi Don't call me sir.. :)

Siva Ganesh Rongala · Answer

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 SNOMOBILENAME from x where x.RowNum=1