Hi,

I am newby and I need help, how to create query.

I have table - Employee with columns - EmployeeID, Department, Salary

How can I get all the Employees ID (EmployeeID), for every department
with minimum salary?

By example - there are the next data in the table
EmployeeID, Department, Salary
1, 1, 900
2, 1, 800
3, 1, 700
4, 2, 100
5, 2, 200
6, 2, 300

And in the end the result has to be:
3
4
Because this is the EmployeeID for every department with minimum salary

Thank you for help!

Best Regards
tony

Tony

Something like should do it;

Select
EmployeeID
From Employees a
join
(
select Min(Salary),
Department
From Employees
group by Department
) b
on
a.department = b.department
and a.salary - b.salary


Chris

Show quoteHide quote

Chris, Thank you very much!

And other question, Is possible to be done this query without "join"?

tony


Chris Hoare написа:
Show quoteHide quote

Not really; because you want to know who and which department using group by
which would be the alternative

e.g.


Two examples

-- Create Dummy Table
declare @test table (empid int, deptid int, salary int)

-- Populate it with some data
insert into @test
Select 1, 1, 1000
insert into @test
Select 2, 1, 5000
insert into @test
Select 3, 1, 10000
insert into @test
Select 4, 1, 1000
insert into @test
Select 5, 2, 1000
insert into @test
Select 6, 2, 1001
insert into @test
Select 7, 2, 3001
insert into @test
Select 8, 2, 3001
insert into @test
Select 9, 2, 3001
insert into @test
Select 10, 2, 3001
insert into @test
Select 11, 3, 1001
insert into @test
Select 12, 3, 2001
insert into @test
Select 13, 3, 3001
insert into @test
Select 14, 3, 4001
insert into @test
Select 7, 3, 5001


-- Query Using Group By
select min(salary), deptid, empid
from @test
group by deptid, empid


-- Original Query with Join (Fixed as i have tested it this time rather than
notepadded it)
Select
empid,
b.Salary,
a.DeptID
From @test a
  join
(
select Min(salary) as Salary,
deptid
From @test
group by deptid
) b
on
a.deptid = b.deptid
and a.salary = b.salary


The query without the join returns all results as each person is a match for
dept / empid. The join returns back the minimum salary by department and then
this gets matched against the employee list to get you the distinct values

Chris


Show quoteHide quote

Hi There,

You may like to try this.
Select * from
(
Select M.*,first_value(salary) over(partition by deptid order by
salary) fv from Yourtable M
)X where salary=fv;


Hope this help.

With Warm regards
Jatinder Singh
http://jatindersingh.blogspot.com
http://sqloracle.tripod.com

Your point of view is very popular today in the world of ms sql server.
Perhaps your framing the questions with the constructs your most familiar
with. Perhaps
this is what you understand by a 'set based' solution. But doesn't a sort
encapsulate the whole problem? And a sort is independent of the numbers of
columns.
I developed the RAC utility to solve these types of problems since they
target specific rows and no aggregation is required. Once you have the
appropriate
sort a ranking can be applied to the result as a byproduct and used as a
predicate to return the appropriate rows. It is only a question of applying
the
appropriate type of rank. It is conceptually simple and conceptually
requires a single pass thru the
data. What I've just described is windows functions/olap from sql-99.
Unfortuneately MS has lagged considerably behind the competition in its
implementation and
done even worse in communicating its nature and benefits. But RAC users know
better:)

Thanks for your sample data. Using ##test=@atest)
Sorting by  deptid, salary and empid and ranking within deptid
gives the whole solution.

Exec Rac @transform='_dummy_',
@rows='deptid & salary & empid',
@pvtcol='Sql*Plus',
@from='##test',
@rowbreak='n',@defaults1='y',
@defaultexceptions='dumy',@racheck='y',
@rowcounters='deptid{deptrank}'

deptid salary empid deptrank
------ ------ ----- --------
1      1000   1     1
1      1000   4     2
1      5000   2     3
1      10000  3     4
2      1000   5     1
2      1001   6     2
2      3001   7     3
2      3001   8     4
2      3001   9     5
2      3001   10    6
3      1001   11    1
3      2001   12    2
3      3001   13    3
3      4001   14    4
3      5001   7     5

All that's required is once the rank is computed is to test it =1. Which
is the minumun salary within the department. In RAC a type of short
circuit is used for any rank greater than 1.

Exec Rac @transform='_dummy_',
@rows='deptid & salary & empid',
@pvtcol='Sql*Plus',
@from='##test',
@rowbreak='n',@defaults1='y',
@defaultexceptions='dumy',@racheck='y',
@rowcounters='deptid{deptrank}',
@wherecounters='deptrank=1'

deptid salary empid deptrank
------ ------ ----- --------
1      1000   1     1
2      1000   5     1
3      1001   11    1

That's it:)
In your example you returned 2 rows for deptid 1 because of a tied min rank.
You can obtain the same granduality here by simply using another rank
method. Just take into account salary within deptid. The rank is only
incremented
for a  new salary, staying the same for equal ones. It resets back to 1
(like
above) for a new deptid.

Exec Rac @transform='_dummy_',
@rows='deptid & salary & empid',
@pvtcol='Sql*Plus',
@from='##test',
@rowbreak='n',@defaults1='y',
@defaultexceptions='dumy',@racheck='y',
@rowindicators='salary{salrank}'

deptid salary empid salrank
------ ------ ----- -------
1      1000   1     1
1      1000   4     1
1      5000   2     2
1      10000  3     3
2      1000   5     1
2      1001   6     2
2      3001   7     3
2      3001   8     3
2      3001   9     3
2      3001   10    3
3      1001   11    1
3      2001   12    2
3      3001   13    3
3      4001   14    4
3      5001   7     5

Now simply test for the 1st rank.

Exec Rac @transform='_dummy_',
@rows='deptid & salary & empid',
@pvtcol='Sql*Plus',
@from='##test',
@rowbreak='n',@defaults1='y',
@defaultexceptions='dumy',@racheck='y',
@rowindicators='salary{salrank}',
@wherecounters='salrank=1'

deptid salary empid salrank
------ ------ ----- -------
1      1000   1     1
1      1000   4     1
2      1000   5     1
3      1001   11    1

Note that the query from Jatinder Singh:

Select * from
(
Select M.*,first_value(salary) over(partition by deptid order by
salary) fv from Yourtable M
)X where salary=fv;

is conceptually the same thing as RAC. The first_value of salary is the
salary with rank=1 given the sort sequence. Oracle does have the best
implementation of olap functions by far. But in general sql is very, very
late to this
game. SAS (statistical analysis system) has had 'olap' functionality for
over 20
years (I modelled RAC's 'last' function from SAS).
One wonders what those people on the sql standards committee due in their
free
time:)

Best,
steve
http://racster.blogspot.com/

Steve Dassin wrote:
Sorry, I know it's not good form to correct spelling/grammar on Usenet,
but I saw the same mistake on your RAC website and now twice in one
sentence so I can't help myself. "your" != "you're".

You're right.

Show quoteHide quote

Well you SOB you are alive!  What's the deal!

Chuck

Show quoteHide quote

Removing unwanted records
Scheduled job or trigger?
Strategy for Handling Missing Required Data
Complex data extract from excel
How to calculate future working date