Re: Simple SQL Question

Here is the solution. Be sure column 'sal' is indexed or you will wait forever:

select a.ename, a.sal, count(*)
from emp a, emp b
where a.sal <= b.sal
group by a.ename, a.sal
having count(*) <= 3

If the count(*) column in the output bothers you, use: create view v_emp as

    select a.ename ename, a.sal sal, count(*) rank     from emp a, emp b
    where a.sal <= b.sal
    group by a.ename, a.sal

select ename, sal from v_emp where rank <= 3

Suresh

nope wrote:

> Kevin Bass wrote:
> >
> > Paulo Odulio:
> >
> > Just add and rownum <= 3 at the end of your WHERE clause. This will give
> > you the first three records being selected. An example is below:
> >
> > select ename, sal
> > from emp
> > where <condition>
> > and rownum <= 3
> > order by sal desc;
> >
>
> Actually, that will limit the number of rows returned from the
> table scan to 3, and will then order those 3 lines. I think the
> more interesting application would be to order all rows, and then only
> show the first 3.
>
> I am not really sure how to do it nicely either, but this one seems
> to give you something close to that. (it depends on how you want to
> treat nonunique salaries.)
>
> This will list the ename for all employees earning equal or more
> to/than the 3rd highest salary. (so more than 3 rows might be returned)
>
> select ename, sal
> from emp
> where sal >= (select max(sal) from emp
> where sal < (select max(sal) from emp
> where sal < (select max(sal) from emp)))
>
> Christian
>
> PS: and it only works if you have at least 3 differing salaries.
> So if anybody has a nice query for that, post it.
>
> >
> > Paulo Odulio wrote in message <352EDF4B.5782278C_at_email.sps.mot.com>...
> > >
> > >
> > >What shall I add to the where clause to be able to
> > >get only the first 3 records?
> > >
> > >select ename, sal
> > >from emp
> > >where <condition>
> > >order by sal desc;
> > >
> > >Thanks,
> > >-Paulo
> > >
Received on Sun Apr 19 1998 - 14:02:17 CDT