Re: MIN and MAX Query

Try the following:

select ename, sal
from emp
where sal in
(
select max(sal)
from emp
)
union
select ename, sal
from emp
where sal in
(
select min(sal)
from emp
)

Joseph Stiehm

"Matty" <matt_at_slippeddisk.fsnenegspamt.co.uk> wrote in message news:<b33k4t$gsq$1_at_news8.svr.pol.co.uk>...
> Hi Again
>
> Thanks for all the replies but I must be doing something stupid cos I get
> errors in them all.
>
> The result I am looking for from the table is as follows:
>
> ENAME SAL
> KING 5000
> SMITH 800
>
> But I either get an "error (select max(sal) from emp)" on the different
> solutions, or get a complete list of all employees with two cols of sal!!!
>
> As I understand it the follwoing gives a complete list
>
> SELECT ENAME, SAL
> FROM emp;
>
> I now need the WHERE statement for where SAL is at its highest and lowest.
> Can u please help as this is starting to bug me!!
>
> BTW I am using Premium Soft MySQL Studio.
>
> Please help!!!!
>
> Matty
>
>
> "TurkBear" <john.greco_at_dot.state.mn.us> wrote in message
> news:e5s95vgr8o7i7b2ik1k9op5v3biinfr0l6_at_4ax.com...
> >
> > Try something along these lines:
> >
> > select 'Highest Paid',a.ename,a.sal,
> > 'Lowest Paid',b.ename ,b.sal
> > from emp a,emp b
> > where a.sal = (select max(sal) from emp)
> > and b.sal = (select min(sal) from emp)
> >
> >
> > Will need some tweaking to fix any duplicates but should be an approach to
> try..
> >
> >
> >
> > "Matty" <matt_at_slippeddisk.fsnenegspamt.co.uk> wrote:
> >
> > >Hi All
> > >
> > >Just starting out making queries with SQL and have become stumped, and am
> > >looking for help!
> > >
> > >Table is called emp, with the following fields:
> > >
> > >EMPNO
> > >ENAME
> > >SAL
> > >
> > >I need to write a query to show the employees name and salary of the
> highest
> > >and lowest salary earner.
> > >
> > >I have worked it out to show the highest and lowest salaries but cannot
> get
> > >it together with the name!!
> > >
> > >I would appreciate your help here!
> > >
> > >Many thanks in advance
> > >
> > >Matty
> > >
> >
Received on Fri Feb 21 2003 - 05:24:40 CET