How to Form Group Query here - Bandi [Resolved]

Posted by Jayakumars under ASP.NET AJAX on 3/31/2015 | Points: 10 | Views : 507 | Status : [Member] [MVP] | Replies : 2
Hi
How to populate Grouping this query

I have Id,EmpId,Empname have here i need EmpId,EmpName only Grouping
can u solve this

Create Table Employee1
(
Id int primary key identity(1,1),
EmpId int,
EmpName varchar(50)
)

Insert into Employee1 values(101,'John')
Insert into Employee1 values(102,'John watson')
Insert into Employee1 values(103,'John')
Insert into Employee1 values(101,'John')
Insert into Employee1 values(102,'John watson')
Insert into Employee1 values(105,'John wesly')
Insert into Employee1 values(106,'John ruban')
Insert into Employee1 values(107,'John Aron')


I need Output this

Id EmpId EmpName
1 101 John
2 102 John watson
3 105 John wesly
4 106 John ruban
5 107 John Aron

Mark as Answer if its helpful to you


Responses

Posted by: Bandi on: 4/1/2015 [Member] [MVP] Platinum | Points: 50

Up
1
Down

Resolved
SELECT ROW_NUMBER() OVER(ORDER BY EmpId) Id	,EmpId, EmpName 
FROM( SELECT EmpId, EmpName , DENSE_RANK() OVER( PARTITION BY EmpName ORDER BY Id) DenseRank FROM Employee1) Temp Where DenseRank=1

/*
OutPut:

Id EmpId EmpName
1 101 John
2 102 John watson
3 105 John wesly
4 106 John ruban
5 107 John Aron */


Mark This Response as Answer
--
Chandu
http://www.dotnetfunda.com/images/dnfmvp.gif

Jayakumars, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Rajnilari2015 on: 3/31/2015 [Member] [Microsoft_MVP] [MVP] Platinum | Points: 25

Up
0
Down
Try this

SELECT 

Id
,EmpId
,EmpName
FROM (
SELECT
Rn = ROW_NUMBER() OVER(PARTITION BY EmpName ORDER BY Id)
,Id
,EmpId
,EmpName
FROM Employee1
)X
WHERE Rn = 1
ORDER BY EmpId


--
Thanks & Regards,
RNA Team

Jayakumars, if this helps please login to Mark As Answer. | Alert Moderator

Login to post response