Re: Tricky SQL Problem: Top "n" of queried records

In article <6f95kq$jhk$1_at_nnrp1.dejanews.com>,   bmuller_at_kenan.com wrote:
>
> In article <6f6f4n$mpo$1_at_nnrp1.dejanews.com>,
> larionov_at_yahoo.com wrote:
> >
> > How about just like:
> >
> > select something
> > from table
> > where rownum < 4;
>
> If there is no specific order required, this would work fine. If you need
> an ordered result set, this won't work (example below).
 ...
>
> In Sybase, this session:
>
> > set rowcount 3
> > go
> > select id, name from sysobjects
> > order by id
> > go
>
> returns:
>
> id name
> ---------- ------------
> 1 sysobjects
> 2 sysindexes
> 3 syscolumns
>
> And it runs very quickly - definitely subsecond.
>
> In Oracle, this session:
>
> SQL> select object_id, object_name from all_objects
> 2> where ROWNUM < 4
> 3> order by object_id
>
> returns:
>
> OBJECT_ID OBJECT_NAME
> ---------- ---------------
> 78 ACCESS$
> 934 ALL_ARGUMENTS
> 984 ALL_ALL_TABLES
>
> very quickly - also subsecond, but it does not represent the correct
> dataset.
> The first three object_id values should be 3, 4, and 5.
>
Granted that it's not correct, it is however the limit of what you can expect from SQL where ordering is an operation carried out after selection and aggregation. Sybase's workaround is quite elegant and does the job. In Oracle, you should create an ordered cursor, open it, and fetch only the first N rows. There is no way to do this in Oracle's command-line query tool, sqlplus, without resorting to a PL/SQL block and using the DBMS_OUTPUT package if the results are to be displayed; this practical problem is not experienced in embedded code, where opening a cursor and fetching rows is normal procedure.

There is a trade-off between the convenience of a Sybase-style "set rowcount" fix and the danger that you might accidentally set it and forget it, resulting in loss of data (not knowing the details of Sybase's solution I don't know whether this is an applicable problem). Whatever, if Oracle thought of this, they presumably rejected it because of the potential danger.

--
Sherilyn

-----== Posted via Deja News, The Leader in Internet Discussion ==-----
http://www.dejanews.com/   Now offering spam-free web-based newsreading