First in sequence

A java group put this out. Is there a way to make it work in SQL 2000 (works
fine in SQL 2005 using a partition by statement - only looking for a 2000
method)
create table #tt(id int,name varchar(3),salary int)
insert into #tt(id , name ,salary)select 10 as id, 'aaa'as name ,
3000 as salary
insert into #tt(id , name ,salary)select 10 , 'abc' , 3890
insert into #tt(id , name ,salary)select 10 , 'bbb', 2000
insert into #tt(id , name ,salary)select 20 , 'xyz' , 10000
insert into #tt(id , name ,salary)select 20 , 'are', 4000
insert into #tt(id , name ,salary)select 50 , 'yyy' , 5000
select a.id, a.name, a.salary
from #tt a
where (a.id, a.salary) in
(select b.id, min(b.salary)
from #tt b
group by b.id
)
Regards,
Jamie
Try:
select
a.id, a.name, a.salary
from
#tt a
inner join
(
select [id], min(salary) as min_salary
from #tt
group by [id]
) as b
on a.[id] = b.[id] and a.salary = b.min_salary
go
AMB
"thejamie" wrote:

> A java group put this out. Is there a way to make it work in SQL 2000 (works
> fine in SQL 2005 using a partition by statement - only looking for a 2000
> method)
> create table #tt(id int,name varchar(3),salary int)
> insert into #tt(id , name ,salary)select 10 as id, 'aaa'as name ,
> 3000 as salary
> insert into #tt(id , name ,salary)select 10 , 'abc' , 3890
> insert into #tt(id , name ,salary)select 10 , 'bbb', 2000
> insert into #tt(id , name ,salary)select 20 , 'xyz' , 10000
> insert into #tt(id , name ,salary)select 20 , 'are', 4000
> insert into #tt(id , name ,salary)select 50 , 'yyy' , 5000
> select a.id, a.name, a.salary
> from #tt a
> where (a.id, a.salary) in
> (select b.id, min(b.salary)
> from #tt b
> group by b.id
> )
> --
> Regards,
> Jamie