Re: How can I cut off a query after 3 lines?

From: Eric D. Pierce <PierceED_at_csus.edu>
Date: Fri, 04 May 2001 12:28:50 -0700
Message-ID: <F001.002F9B5B.20010504105046@fatcity.com>

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

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: david.jones_at_acnielsen.co.uk

------------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