Oracle Pl/sql return one cursor from multiple queries

Question

I have a stored procedure, which returns a cursor.

The application passes a parameter to the procedure which determines how many ID`s should be fetched, so the procedure doesnt know a head of time that number.

foreach ID i need to fetch the top 3 records with that ID. what I have tried is using:

select * from table_name where id in (List of ID`s);

That query works, but i cant get the top 3 of each ID. If i limit the result count, i will get the TOP results of the first ID.

I thought using For Loop, executing the query for each ID and append the results to the cursor, but as I understand it`s impossible.

Any Ideas ?

More details Lets say I have 5 IDs and each of them have inner Ids so Id 1 has (1,2,3,4,5) Id 2 (1,2,3,4,5) Id 3 (12,14,15,16,22) Id 4 (2,3,5,7,9) Id 5 (4,7,8,9,10) In this case, which is the case I am dealing with, I dont see how row number will help me. I need the top 3 for each ID, in this case the cursor should have 15 results.

10x alot and have a good weekend ;)

APC · Accepted Answer · 2010-11-28 19:53:34Z

5

Presumably you have some criterion for determining the top three?

Anyway, the way to achieve this end is with an analytic function. Oracle offers three distinct functions: ROW_NUMBER(), RANK() and DENSE_RANK(). These offer three slightly different interpretations of TOP 3. Find out more.

Here is the basic idea, using ROW_NUMBER(), which will return exactly three rows for each ID.

open rc for 
    select * from (
        select t.*
               , row_number() over (partition by id order by whatever ) rn 
        from table_name t 
        where t.id in (List of ID`s)
     )
     where rn <= 3;

The whatever in the ROW_NUMBER() clause is the column you use to determine TOP-ness.

edited Nov 28, 2010 at 19:53

answered Nov 27, 2010 at 17:42

APC

147k20 gold badges180 silver badges289 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

SuperFrog Over a year ago

I have a way to determine the top 3 for each od the ID`s sepertaly.

OMG Ponies Over a year ago

@Udi l: That's what the PARTITION BY id portion of an analytic/ranking/windowing function does (in this case ROW_NUMBER).

SuperFrog Over a year ago

You soloution works perfectly, It`s exactly what I needed. 10x alot.

torzech · Accepted Answer · 2010-11-27 18:04:57Z

-2

Another idea to go with would be to define an Oracle temporary table

create global temporary table temp_table_name

Link to more information

Then in a for loop you can insert rows into the temp table for all of the needed ids. The cursor returned would then be the content of the temp table. Of course this solution only makes sense when it is not possible to get the result back from a single sql query

answered Nov 27, 2010 at 18:04

torzech

4045 silver badges8 bronze badges

2 Comments

SuperFrog Over a year ago

I want to emphasize that the preformance for this query is critical and shoud execute in the fastest way possible. 10x :)

APC Over a year ago

Well it is perfectly possible to get the result back from a single query, so a global temporary table is an unnecessary overhead.

Collectives™ on Stack Overflow

Oracle Pl/sql return one cursor from multiple queries

2 Answers 2

3 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related