query help

[prodadmin]

hello

we have order types and orders tables and for every ordertype we have many orders

i am looking for a query to dispay 5 orders for every ordertype

how can we get this

thanks

[tamilselvan]

Assume both tables have type column.

select ...
from ordertype a, orders b
where a.type = b.type
and rownum <= 5;

[PAVB]

prodadmin said "dispay 5 orders for every ordertype"
...

select ...
from ordertype a, orders b
where a.type = b.type
and rownum <= 5;

I'm wondering how this query solves that business requirement?

[prodadmin]

hello tamilselvan , query you provided results last 5 rows

i am looking for some thing like this

ordertype -orderid
1-12
1-13
1-14
1-15
1-16
2-34
2-45
2-67
2-78
3-99
3-98
3-97



thank you

[PAVB]

My preferred solutions will involve two cursors.

Outer cursor for order_type and inner cursor for the five orders you want to show for each order_type.

[tamilselvan]

Sorry, Analytic function will solve the problem

      select type, order_id, 
               row_number() 
               over (partition by type order by order_id) RN  
      from   (  select a.type , b.order_id
                    from ordertype a, orders b
                  where a.type = b.type)
     where RN <= 5;