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 TrivediPosted Apr 25, 2013, 11:23 PM
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.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
also it can be possible with CTE.
hope this will help you.
Jignesh TrivediPosted Apr 26, 2013, 4:14 AM
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 area,City
with cube
hope this will help you.
Ravi ShekharPosted Apr 26, 2013, 3:27 AM
Jignesh TrivediPosted Apr 26, 2013, 3:24 AM
Don't call me sir.. :)
Ravi ShekharPosted Apr 26, 2013, 2:52 AM
Hemant SrivastavaPosted Apr 25, 2013, 9:57 AM
SELECT DISTINCT (MOBIL + NAME)
FROM YOUR_TABLE
Siva Ganesh RongalaPosted Apr 25, 2013, 9:45 AM
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