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

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 article <35168C67.5A454A63_at_ti.com>,
> p-chang_at_ti.com wrote:
> >
> > select a.ord_col, a.other_stuff
> > from table_name a
> > where 3>(select count(*)
> > from table_name b
> > where b.ord_col < a.ord_col
> > )
> > order by a.ord_col;

I don't know yet whether or not this works - it's still running. It's been running for around 15 minutes now.

> >
> > Johan Thorselius wrote:
> >
> > > Brenda Muller wrote:
> > >
> > > > In Sybase (and Sybase alone), you can "set rowcount 3" at the
 beginning
 of your
> > > > transaction, which will restrict the server to returning only 3 rows.
 There is
> > > > nothing analogous to this in Oracle.
> > > >
> > > > Brenda Muller
> > > >
> > >
> > > Yes, the following should be equiv. in Oracle:
> > >
> > > SELECT deptno, sum(salary) FROM emp
> > > GROUP BY deptno
> > > ...order by something relevant....
> > > WHERE ROWNUM < 4;
> > >

No, it's not possible to GROUP BY and ORDER BY and then specify a WHERE clause. Logically, this is what you want to do, but it's illegal syntax in Oracle. Put the WHERE clause in it's proper place and then the first four rows are returned to the initial data set, which is then grouped and ordered (i.e., the wrong answer).

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

In Oracle, this session:

SQL> select object_id, object_name from all_objects
  2> where ROWNUM < 4
  3> order by object_id

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.

Don't know about p-chang's suggestion. It may produce the correct results, but it's still running and therefore is not analogous.

Brenda

-----== Posted via Deja News, The Leader in Internet Discussion ==----- http://www.dejanews.com/ Now offering spam-free web-based newsreading Received on Tue Mar 24 1998 - 00:00:00 CET