CeDeT: OIF (Oracle is funny (that way)) #6

Top n ordered by or not

Getting the top n ordered by something can sometimes be a tricky thing!

The test

SQL> select * from v$version where rownum<=2;

BANNER
----------------------------------------------------------------
Oracle8i Enterprise Edition Release 8.1.7.2.0 - Production
PL/SQL Release 8.1.7.2.0 - Production

SQL> create table ox(
  2    ox_id varchar2(10) not null,
  3    ox_size number not null,
  4    primary key(ox_id)
  5  );

Tabel er oprettet.

SQL> begin
  2    for i in 1..20 loop
  3      insert into ox(ox_id,ox_size) values(chr(ascii('a')+i),i*100);
  4    end loop;
  5  end;
  6  /

PL/SQL-procedure er udført.

SQL> commit;

Bekræftelse er udført.

SQL> set pagesize 50
SQL> select * from ox;

OX_ID         OX_SIZE
---------- ----------
b                 100
c                 200
d                 300
e                 400
f                 500
g                 600
h                 700
i                 800
j                 900
k                1000
l                1100
m                1200
n                1300
o                1400
p                1500
q                1600
r                1700
s                1800
t                1900
u                2000

20 rækker er valgt.

SQL> prompt Ordering by size
Ordering by size
SQL> select o1.ox_id,o1.ox_size from ox o1 order by 2 desc;

OX_ID         OX_SIZE
---------- ----------
u                2000
t                1900
s                1800
r                1700
q                1600
p                1500
o                1400
n                1300
m                1200
l                1100
k                1000
j                 900
i                 800
h                 700
g                 600
f                 500
e                 400
d                 300
c                 200
b                 100

20 rækker er valgt.

SQL> prompt Top 5 ordered by size
Top 5 ordered by size
SQL> select * from (
  2  select o1.ox_id,o1.ox_size from ox o1 order by 2 desc
  3  ) where rownum<=5;

OX_ID         OX_SIZE
---------- ----------
u                2000
t                1900
s                1800
r                1700
q                1600

SQL> prompt Order by size including a total
Order by size including a total
SQL> select o1.ox_id,o1.ox_size from ox o1
  2  union
  3  select 'total',sum(o2.ox_size) from ox o2
  4  order by 2 desc;

OX_ID         OX_SIZE
---------- ----------
total           21000
u                2000
t                1900
s                1800
r                1700
q                1600
p                1500
o                1400
n                1300
m                1200
l                1100
k                1000
j                 900
i                 800
h                 700
g                 600
f                 500
e                 400
d                 300
c                 200
b                 100

21 rækker er valgt.

SQL> prompt Top 5 ordered by size including a total
Top 5 ordered by size including a total
SQL> select * from (
  2  select o1.ox_id,o1.ox_size from ox o1
  3  union
  4  select 'total',sum(o2.ox_size) from ox o2
  5  order by 2 desc
  6  ) where rownum<=5;

OX_ID         OX_SIZE
---------- ----------
b                 100
c                 200
d                 300
e                 400
f                 500