RE: Top N query

From: Djordje Jankovic <djankovic_at_corp.attcanada.ca>
Date: Wed, 10 Jan 2001 15:48:19 -0500
Message-Id: <10737.126333@fatcity.com>

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." Received on Wed Jan 10 2001 - 14:48:19 CST