CeDeT: OIF (Oracle is funny (that way)) #6
Getting the top n ordered by something can sometimes be a tricky thing!
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