Re: SQL Question, first 50 of a query

Josef Scheichenzuber wrote:
>
> In article <4ntehe$o8j_at_kadath.zeitgeist.net>, michael_at_lazlo.steam.com
> (Michael Brodesky) wrote:
>
> > I am trying to do the top50 in sql that is provided by
> > access, but in oracle 7.2. Anyone know how to do this?
> > Also is there a way to rename a column in a table?
>
> Try the following to get the top50 from your_table:
>
> select * from your_table where rownum <= 50;
>
> Josef.

The rownum restriction is done BEFORE any sorting, so an "order by my_qty" tacked on the end will just give you an ordered set of the first 50 rows in the table (from whatever method the system defaults to retrieving them with; not necessarily the first 50 physical rows), not the first 50 after ordering.

Here's one technique I've used in the past: 3/6/89 by John C. Darrow
A common concern is how to select the top n (top 5, top 50, etc.) of some set. Consider the inventory file, inv, created as follows:

   create table inv (product char(10), qty number);

If we want the top 5 products by quantity, qty, we can proceed as follows:

   create view distinv as select distinct qty from inv;    create view ordered_inv as (select 1 seq,max(qty) qty from inv

      union select count(*)+1 seq,a.qty qty from distinv a, inv b
            where b.qty > a.qty group by a.qty);

These two views result in view ordered_inv having the top qty associated with sequence (seq) 1, the next with 2, and so on. The intermediate view distinv is necessary so that "tied" values (e.g. 500,400,300,300,200 in descending order) come out right. (Without the distinv, the "first" 300 would have 2 values higher than it, and so would the "second" 300, so we would think there were 4 values greater than 300 and place it 5th instead of 3rd.) We can then select the top 5 as follows:

   select * from inv where qty in
   (select qty from ordered_inv where seq <=5); or: select * from inv a where exists

   (select 'X' from ordered_inv b where seq <=5 and a.qty = b.qty); Received on Fri May 24 1996 - 00:00:00 CEST