Grouping

I'm trying to write a report and need to group columns that are alike.
For example.

DeptID JobTitle

40344 Sales Clerk
1st Assistant
Store Manager

40666 Sales Clerk
2nd Assistant
Store Manager

Sorry, it will not output correctly, I hope you get what I'm trying to do.
I can get the results above but it puts the deptid for each job title. I do not want it. Any help will be appreciated. Thanks in advance.Why? This is just a display issue. Can't you handle it in the frontend?|||Not if its just a query. I was thinking about doing the whole cursor deal and looping through that to give me my desired results but i thought it would be an easier way.|||You could do it as two selects. Select into a table variable with an identity column. Then select from the table variable and show blank if it's not the max(identity) for the given DeptID. Make sense?|||Yes, thank you.|||What about appending a carriage return to the last field: + char(13)?|||What about appending a carriage return to the last field: + char(13)?If you pursue that route, you need to "crelf it"... Instead of just a carriage return, you need a carriage return followed by a line feed which is: + Char(13) + Char(10)

-PatP

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