query help

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

Assume both tables have type column.

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

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

Quote:

---

Originally Posted by tamilselvan

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:

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

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.

Sorry, Analytic function will solve the problem

Code:

---

      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;

---