Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Mailing Lists -> Oracle-L -> Re: How can I cut off a query after 3 lines?
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." <ORACLE-L_at_DBINFO.COM>
F r o m : "Jones, David (Oxford)" <David.Jones_at_ACNIELSEN.CO.UK>
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
select 'The '||'&1'||' '||'&2'||' value'||
decode(upper(substr( '&2' ,2,1)), 'O', 's', '')|| ' for '||upper( '&3' )||' in '||upper( '&4' ) headfrom 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 )
spool off
set verify on
set termout on
set hea on
undef head
undef topbot
undef allorone
undef maxormin
undef bracket
clear columns
_
- Regards
------------And now a message from today's ORACLE-L sponsor---------------
To subscribe, unsubscribe or change subscription options to ORACLE-L, send a message to LISTSERV_at_dbinfo.com. 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: PierceED_at_csus.edu 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: ListGuru_at_fatcity.com (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).Received on Fri May 04 2001 - 14:28:50 CDT