Distinct - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Distinct

Author

Topic

cool.mugil
Starting Member

32 Posts

Posted - 2010-03-18 : 07:26:42

Hi,
My table structure is like this,

S.no, EmployeeID
1, 1
2, 1
3, 2
4, 2
5, 3
6, 3

i want to take unique employeeid with s.no.How to write the query.Please help me?

My result should be like this,

S.no EmployeeID
1, 1
3, 2
5, 3

Thanks ,
Mugil

haroon2k9
Constraint Violating Yak Guru

328 Posts

Posted - 2010-03-18 : 07:30:56

select S.no, EmployeeID from
(
select seq=row_number()over(partition by EmployeeID order by s.no),s.no,employeeid from tbl
)t where seq=1

vaibhavktiwari83
Aged Yak Warrior

843 Posts

Posted - 2010-03-18 : 07:31:39

create table tst ( Sno int , EmployeeID int )
go
insert into tst
SELECT 1, 1
union all
SELECT 2, 1
union all
SELECT 3, 2
union all
SELECT 4, 2
union all
SELECT 5, 3
union all
SELECT 6, 3
go

SELECT sno, employeeid
FROM
(
SELECT ROW_NUMBER() OVER( Partition by EmployeeID order by sno) seq, * FROM tst
)a WHERE seq = 1

Vaibhav T

cool.mugil
Starting Member

32 Posts

Posted - 2010-03-18 : 07:40:07

Thanks haroon2k9 it solved my problem

quote:
Originally posted by haroon2k9

select S.no, EmployeeID from
(
select seq=row_number()over(partition by EmployeeID order by s.no),s.no,employeeid from tbl
)t where seq=1

haroon2k9
Constraint Violating Yak Guru

328 Posts

Posted - 2010-03-18 : 07:40:50

quote:
Originally posted by cool.mugil

Thanks haroon2k9 it solved my problem

quote:
Originally posted by haroon2k9

select S.no, EmployeeID from
(
select seq=row_number()over(partition by EmployeeID order by s.no),s.no,employeeid from tbl
)t where seq=1

welcome.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-03-18 : 08:42:09

Also see what you can do with row_number() function
http://beyondrelational.com/blogs/madhivanan/archive/2007/08/27/multipurpose-row-number-function.aspx

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources