UNTESTED, PLEASE VERIFY BEFORE USE:
---original message follows---
D a t e : Fri, 11 Jul 1997 14:41:53 +0100
R e p l y - T o : "ORACLE database mailing list." <[EMAIL PROTECTED]>
F r o m : "Jones, David (Oxford)" <[EMAIL PROTECTED]>
S u b j e c t : Can i write a query for this.................
This utility gives top/bottom values for a specified column in a
specified table. If you need other columns you will need to adapt it a
little. I would not recommend it on large tables if the column is not
indexed. Whether indexed or not, you can almost certainly do it more
simply in PL/SQL (Define a cursor with order by, loop through n
times.............?)
HTH.
rem ...............................................................
rem ..... Program Name: GIVEME.SQL
rem ..... Written by: David Jones - January 1995
rem ..... Purpose: Returns either:
rem ..... - The top 'n' values for a column
rem ..... - The bottom 'n' values for a column
rem ..... - The 'nth' value for a column
rem ..... - the 'nth lowest' value in a column
rem ..... If the nth (or nth lowest) value is 'x',
rem ..... all the records with a value of 'x' will be rem
..... returned.
rem ..... Called by: Any user
rem ..... Calls: nothing
rem ..... Tables: as specified in input variable 4
rem ..... Syntax: The following are all valid:
rem ..... GIVEME 6 top column_name table_name
rem ..... GIVEME 6 bottom column_name table_name
rem .....
rem ..... GIVEME 1 st column_name table_name
rem ..... GIVEME 2 nd column_name table_name
rem ..... GIVEME 3 rd column_name table_name
rem ..... GIVEME 4 th column_name table_name
rem ..... etc......
rem ..... GIVEME 1 stlast column_name table_name
rem ..... GIVEME 2 ndlast column_name table_name
rem ..... GIVEME 3 rdlast column_name table_name
rem ..... GIVEME 4 thlast column_name table_name
rem ..... etc......
rem ..... (can abbreviate bottom to bot, stlast to stl,
rem ..... ndlast to ndl etc.)
rem ................................................................
set pages 96
set lines 110
set hea off
set verify off
set termout off
col head new_v head
col topbot new_v topbot
col allorone new_v allorone
col maxormin new_v maxormin
col bracket new_v bracket
select decode(upper(substr(ltrim(rtrim('&2')),3,1)),
'P', '>',
'', '>',
'T', '<',
'L', '<',
'>') topbot,
decode(upper(substr(ltrim(rtrim('&2')),3,1)),
'P', '',
'T', '',
'L', 'max(',
'', 'min(',
'' ) maxormin,
decode(upper(substr(ltrim(rtrim('&2')),3,1)),
'P', '',
'T', '',
'L', ')',
'', ')',
'' ) bracket
from sys.dual
/
select 'The '||'&1'||' '||'&2'||' value'||
decode(upper(substr( '&2' ,2,1)), 'O', 's', '')||
' for '||upper( '&3' )||' in '||upper( '&4' ) head
from dual
/
spool giveme
prompt &head
prompt
select &maxormin a.&3 &bracket
from &4 a
where exists
(select 'How many are bigger/smaller than me?'
from &4 b
where b.&3 &topbot a.&3
having count(*) < &1 )
order by 1 desc
/
spool off
set verify on
set termout on
set hea on
undef head
undef topbot
undef allorone
undef maxormin
undef bracket
clear columns
_
- Regards
----------------------------------------------------------------------
David Jones
Oracle DBA, Systems Dept, Tel : (01865) 742742 Ext:
2507
ACNielsen, Oxford. OX3 9RX e-mail: [EMAIL PROTECTED]
------------And now a message from today's ORACLE-L sponsor---------------
To subscribe, unsubscribe or change subscription options
to ORACLE-L, send a message to [EMAIL PROTECTED]
ORACLE-L is a service of Kapur Business Systems, Inc.
--------------------------------------------------------------------------
---end---
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Eric D. Pierce
INET: [EMAIL PROTECTED]
Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
San Diego, California -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
