Distinct Query - 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

SQL Server 2005 Forums

Transact-SQL (2005)

Distinct Query

Author

Topic

misterraj
Yak Posting Veteran

94 Posts

Posted - 2008-07-31 : 05:51:10

---------------------
Empid Sno Salary
----------------------
10 1 2000
10 2 3000
10 3 4000
20 4 5000
20 5 6000
20 6 7000
30 7 8000
30 8 9000
30 9 10000
------------------------

I have a table like this, what is want is a result like this
10 1 2000
20 4 5000
30 7 8000

I need only one EmpID for the person with records, the least s.no would be displayed.

how would i write the query help please

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-07-31 : 05:55:14

Select empid,min(sno) as sno, min(salary) as salary from table
group by empid

Madhivanan

Failing to plan is Planning to fail

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-07-31 : 05:55:51

[code]select Empid, Sno, Salary
from (
select Empid, Sno, Salary,
row_no = row_number() over (partition by Empid order by Sno)
from yourtable
) e
where e.row_no = 1[/code]

KH
[spoiler]Time is always against us[/spoiler]

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-07-31 : 05:57:20


select t.Empid, Sno, Salary
from   yourtable t
       inner join
       (
           select Empid, Sno = min(Sno)
           from   yourtable
           group by Empid
       ) m on  t.Empid = m.Empid
           and t.Sno   = m.Sno

KH
[spoiler]Time is always against us[/spoiler]

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-07-31 : 05:58:23

quote:
Originally posted by madhivanan

Select empid,min(sno) as sno, min(salary) as salary from table
group by empid

Madhivanan

Failing to plan is Planning to fail

OP only wanted record with min Sno. Min Sno does not implied min Salary

KH
[spoiler]Time is always against us[/spoiler]

misterraj
Yak Posting Veteran

94 Posts

Posted - 2008-07-31 : 06:05:00

Everything worked great, thanks to all the members who gave the lighting reply! I m very happy.. thanks once again

misterraj
Yak Posting Veteran

94 Posts

Posted - 2008-07-31 : 06:07:05

yes i agree Min sNo doesnot implied Min salary, if i had this,

Select empid,min(sno) as sno, salary as salary from table
group by empid

it doesnot work, the last two queries worked great. anyway thanks to all!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources