Get the ID from Min(grouppingcolumn)

Hi,
I have a simple query,
select emp_group,min(salary) from employees group by emp_group.
now I get the results.
Is there a way to get the ID(Primary key Column) value for which the
query returned minimum salary.
Thanks
Kiranselect e.ID,e.emp_group,e.salary
from employees e
inner join (
select emp_group,min(salary)
from employees
group by emp_group) as X(emp_group,minsalary)
on e.emp_group=X.emp_group and e.salary=X.minsalary
Note that you may get multiple rows back for
a particular minimum salary in a group.|||Your result could be not unique as you are only getting the min salary
of a specific emp_group (which I assume is anattribute for
allemployees, so it could be that more than one employee has a specific
emp_group). Therefore you can only get the set of employees (could be
one or more) who have the min salary.
SELECT <columnlist>
FROM employees
WHERE salary IN --OR "="
(
select emp_group,min(salary) from employees group by emp_group
)
HTH, Jens Suessmeyer.|||Hi Kiran
you'll need to join the MIN query to the same table it's based on.
See the thread here:
http://groups.google.com/group/micr...b0e4da5f8f24d99
cheers
Seb|||markc600@.hotmail.com wrote:
> select e.ID,e.emp_group,e.salary
> from employees e
> inner join (
> select emp_group,min(salary)
> from employees
> group by emp_group) as X(emp_group,minsalary)
> on e.emp_group=X.emp_group and e.salary=X.minsalary
> Note that you may get multiple rows back for
> a particular minimum salary in a group.
>
Thanks a lot, that's what I was looking for.
what if it returns multiple rows of min sal in a group and I want the top 1.
Thanks
Kiran|||top 1 based on what criteria?|||sebt wrote:
> Hi Kiran
> you'll need to join the MIN query to the same table it's based on.
> See the thread here:
> http://groups.google.com/group/micr...b0e4da5f8f24d99
> cheers
> Seb
>
Thanks|||markc600@.hotmail.com wrote:
> top 1 based on what criteria?
>
if a group has same min. slaray with two rows, I need the top 1.
based on e.ID
Thanks
Kiran|||select min(e.ID) as ID,e.emp_group,e.salary
from employees e
inner join (
select emp_group,min(salary)
from employees
group by emp_group) as X(emp_group,minsalary)
on e.emp_group=X.emp_group and e.salary=X.minsalary
group by e.emp_group,e.salary|||markc600@.hotmail.com wrote:
> select min(e.ID) as ID,e.emp_group,e.salary
> from employees e
> inner join (
> select emp_group,min(salary)
> from employees
> group by emp_group) as X(emp_group,minsalary)
> on e.emp_group=X.emp_group and e.salary=X.minsalary
> group by e.emp_group,e.salary
>
Thanks a lot Mark