group by Aggregate

I have the following table.
id Deptid Staffid Salary
1 1 100 85000
2 1 101 75000
3 2 201 90000
3 2 202 35000
I like to find the minumim salary for each dept and its corresponding
staffid value.
Thus.. the result should be...
Deptid Salary Staffid
1 75000 101
2 35000 202
select deptid,min(salary) from test
group by deptid
will yield the correct result less the staffid. How do I capture the
staffid for the min(salary)?
create table test (id int,deptid int, staffid int,salary int)
insert into test values (1,1,100,85000)
insert into test values (2,1,101,75000)
insert into test values (3,2,201,90000)
insert into test values (3,2,202,35000)
Many thanks.
ShahriarHi
untested
SELECT * FROM Table WHERE Salary =(SELECT MIN(Salary) FROM Table T
WHERE T.Deptid =Table.Deptid AND T.id <=Table.id)
"Shahriar" <HelloShahriar@.hotmail.com> wrote in message
news:HphNf.10696$XE6.4888@.trnddc07...
> I have the following table.
> id Deptid Staffid Salary
> 1 1 100 85000
> 2 1 101 75000
> 3 2 201 90000
> 3 2 202 35000
> I like to find the minumim salary for each dept and its corresponding
> staffid value.
> Thus.. the result should be...
> Deptid Salary Staffid
> 1 75000 101
> 2 35000 202
> select deptid,min(salary) from test
> group by deptid
> will yield the correct result less the staffid. How do I capture the
> staffid for the min(salary)?
> create table test (id int,deptid int, staffid int,salary int)
> insert into test values (1,1,100,85000)
> insert into test values (2,1,101,75000)
> insert into test values (3,2,201,90000)
> insert into test values (3,2,202,35000)
>
> Many thanks.
> Shahriar
>
>|||SELECT a.DeptID,a.Salary,a.Staffid
FROM test a
INNER JOIN(
SELECT MIN(Salary),DeptID
FROM test
GROUP BY DeptID) b(Salary,DeptID) ON a.Salary=b.Salary
AND a.DeptID=b.DeptID|||Shahriar wrote:
> I have the following table.
> id Deptid Staffid Salary
> 1 1 100 85000
> 2 1 101 75000
> 3 2 201 90000
> 3 2 202 35000
> I like to find the minumim salary for each dept and its corresponding
> staffid value.
> Thus.. the result should be...
> Deptid Salary Staffid
> 1 75000 101
> 2 35000 202
> select deptid,min(salary) from test
> group by deptid
> will yield the correct result less the staffid. How do I capture the
> staffid for the min(salary)?
> create table test (id int,deptid int, staffid int,salary int)
> insert into test values (1,1,100,85000)
> insert into test values (2,1,101,75000)
> insert into test values (3,2,201,90000)
> insert into test values (3,2,202,35000)
>
> Many thanks.
> Shahriar
What are the keys? What if there is more than one person with the same
minimum salary for one department?
Try:
SELECT id, deptid, staffid, salary
FROM test AS T
WHERE salary =
(SELECT MIN(salary)
FROM test
WHERE deptid = T.deptid);
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--