Re: Simple SQL Question

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 Mon Apr 13 1998 - 10:36:15 CDT