Re: Simple Query of multiple record

Martyn Cavett wrote:
>
> Want you want is (something like)
>
> SELECT name
> FROM emp
> WHERE sal > ( SELECT sal
> FROM emp
> WHERE rownum < 4
> ORDER BY SAL DESC)
> /
>
> Joseph Jao <jjao_at_idt.net> wrote in article <33273FF8.F08_at_idt.net>...
> > Hi, I tried to make a simple SQL command query to get the names of 3 top
> > paid persons. "Select name, sal from emp where rownum<4 order by sal
> > desc;" doesn't work. "Select name, sal from emp order by sal desc;"
> > works but it'll bring everything. Can anyone help me?
> >
> > jjao_at_idt.net
> >

This is one of the many kinds of query which are *only* difficult in SQL*Plus. The reason is that SQL*Plus (without PL/SQL) is the only user interface which allows the user almost no control over the fetching of rows from a cursor (where rownum <[=] ... ] is useless for an ordered set).
In almost all other interfaces you declare the query with an _order by_ clause and just fetch the first n rows.

I use this as an example to support my contention that SQL*Plus (without PL/SQL), while an invaluable development and debugging tool, is of no real use in _real life_ applications. (Other examples are that re-executions of the same statement are always re-parsed and that only a single cursor is executable at one time)

Chrysalis. Received on Thu Mar 13 1997 - 00:00:00 CST