CODEWITHSUNDEEP
sqloracle-databaseplsql
UpTide
UpTide

Reputation: 358

for each item in list, oracle pl/sql

I have a query:

select sq1.* from (
    select * from table1
    where type = '1'
    order by name
) sq1
where rownum <= 10;

but I have to run this list for types 2,3,a,b,c too. Is there a way to do something like this?

my @list = ('1', '2', '3', 'a', 'b', 'c');
for each my $item in @list {
    select sq1.* from (
        select * from table1
        where type = $item
        order by name
    ) sq1
    where rownum <= 10;
}

(Yeah, my code's in perl. It's all I know anymore.)

Upvotes: 2

Views: 1691

Answers (1)

Ram Limbu
Ram Limbu

Reputation: 442

SELECT sq1.* FROM (
    SELECT 
        tbl.*, 
        ROW_NUMBER() OVER (PARTITION BY type ORDER BY name) AS rnk 
    FROM table1 tbl
    WHERE type IN ('1', '2', '3', 'a', 'b', 'c')

) sq1
WHERE rnk <= 10;

Upvotes: 1

Related Questions