RE: Top N query - RANK() and DENSE

From: Koivu, Lisa <lkoivu_at_qode.com>
Date: Wed, 10 Jan 2001 16:06:01 -0500
Message-Id: <10737.126339@fatcity.com>

This message is in MIME format. Since your mail reader does not understand this format, some or all of this message may not be legible.

------_=_NextPart_001_01C07B49.28BC623C
Content-Type: text/plain;

charset="iso-8859-1"

Thanks Djordje, but actually that isn't exactly what I'm looking for.

The RANK() and DENSE_RANK() functions do what I need, I got it working. Has anyone used these in a large environment? Are they performance killers?

Thanks

-----Original Message-----
From: Djordje Jankovic [mailto:djankovic_at_corp.attcanada.ca] Sent: Wednesday, January 10, 2001 3:48 PM To: 'ORACLE-L_at_fatcity.com'
Cc: 'lkoivu_at_qode.com'
Subject: RE: Top N query

This should do the job:

select category, num
from tab a
where 3 > (select count(*)

                from tab b
               where a.category = b.category
                 and a.num < b.num)

order by 1 asc, 2 desc
/

HTH Djordje

-----Original Message-----
From: Koivu, Lisa [mailto:lkoivu_at_qode.com] Sent: Wednesday, January 10, 2001 3:01 PM To: Multiple recipients of list ORACLE-L Subject: Top N query

OK, I apologize in advance, I know this has been discussed many times.

I need to generate the top N, in this case 3, values for a query, like the example shown below. It has to be done in SQL, because I need to return this as a refcursor. (OPEN refcurcor FOR...)

category1     10 
category1     14 
category1     2 
category1     25 
category2     14 
category2    13 
category2     55

category2 5
category3 14
category3 1
category4 5

I need to display this:

category1 25
category1 14
category1 10
category2 55
category2 14
category2 13
category3 14
category3 1
category4 5

Can you hear me cursing....

Thanks in advance for any suggestions.

Lisa Rutland Koivu
Oracle Database Administrator
Qode.com
4850 North State Road 7
Suite G104
Fort Lauderdale, FL 33319

V: 954.484.3191, x174 
F: 954.484.2933 
C: 954.658.5849

http://www.qode.com <http://www.qode.com>

"The information contained herein does not express the opinion or position of Qode.com and cannot be attributed to or made binding upon Qode.com."

------_=_NextPart_001_01C07B49.28BC623C
Content-Type: text/html;

charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 3.2//EN">
<HTML>
<HEAD>
<META HTTP-EQUIV=3D"Content-Type" CONTENT=3D"text/html; =
charset=3Diso-8859-1">
<META NAME=3D"Generator" CONTENT=3D"MS Exchange Server version =
5.5.2650.12">
<TITLE>RE: Top N query - RANK() and DENSE_RANK()</TITLE>
</HEAD>
<BODY>

Thanks Djordje, but actually that isn't exactly what =
I'm looking for.  


The RANK() and DENSE_RANK() functions do what I need, =
I got it working.  Has anyone used these in a large = environment?  Are they performance killers?

Thanks

 

-----Original Message-----
 From: Djordje Jankovic [<A =
HREF=3D"mailto:djankovic_at_corp.attcanada.ca">mailto:djankovic_at_corp.attcan= ada.ca</A>]
 Sent: Wednesday, January 10, 2001 3:48 PM
 To: 'ORACLE-L_at_fatcity.com'
 Cc: 'lkoivu_at_qode.com'
 Subject: RE: Top N query

 

This should do the job:
  
 select category, num
   from tab a
  where 3 > (select count(*)
           &nb= sp;     from tab b
           &nb= sp;    where a.category =3D b.category
           &nb= sp;      and a.num < b.num)
  order by 1 asc, 2 desc
 /
  
 HTH
  
 Djordje

 

-----Original Message-----
 From: Koivu, Lisa [<A =
HREF=3D"mailto:lkoivu_at_qode.com">mailto:lkoivu_at_qode.com</A>]
 Sent: Wednesday, January 10, 2001 3:01 PM
 To: Multiple recipients of list ORACLE-L
 Subject: Top N query

 
 

OK, I apologize in advance, I know this has been =
discussed many times. 


I need to generate the top N, in this case 3, values =
for a query, like the
 example shown below.  It has to be done in SQL, =
because I need to return
 this as a refcursor.  (OPEN refcurcor FOR...) =



category1     10 
 category1     14 
 category1     2 
 category1     25 
 category2     14 
 category2    13 
 category2     55 
 category2    5 
 category3    14 
 category3    1 
 category4    5 


I need to display this: 


category1  25 
 category1  14 
 category1  10 
 category2  55 
 category2  14 
 category2  13 
 category3  14 
 category3  1 
 category4  5 


Can you hear me cursing.... 


Thanks in advance for any suggestions.  


Lisa Rutland Koivu 
 Oracle Database Administrator 
 Qode.com 
 4850 North State Road 7 
 Suite G104 
 Fort Lauderdale, FL  33319 


V: 954.484.3191, x174 
 F: 954.484.2933 
 C: 954.658.5849 
 <A HREF=3D"http://www.qode.com" =
TARGET=3D"_blank">http://www.qode.com</A> <<A = HREF=3D"http://www.qode.com" =
TARGET=3D"_blank">http://www.qode.com</A>>  


"The information contained herein does not =
Received on Wed Jan 10 2001 - 15:06:01 CST