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

From: Peter Chang <p-chang_at_ti.com>
Date: 1998/03/23
Message-ID: <35168C67.5A454A63@ti.com>#1/1

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;

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;
>
> But you must sort the resultset so the rows you are interested in are placed first.
>
> Best regards
>
> Johan Thorselius
Received on Mon Mar 23 1998 - 00:00:00 CST