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? :rolleyes:

[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;