ROW_NUMBER by any ID (or any other kind of solution)

Question

I'm trying to create a select to assign to a variable a correlative number to the rows, but by a common id field, not a correlative number for all search result.

example for the ORDER table:

order_id   product_name
-----------------------
007        bicicle
007        bike 
007        car
008        car
008        ship
009        airplane
009        rocket
009        bicicle

what I need?

I need to add a correlative number at the first column by order_id, as

row_number  order_id   product_name
-----------------------------------
1           007        bicicle
2           007        bike 
3           007        car
1           008        car
2           008        ship
1           009        airplane
2           009        rocket
3           009        bicicle

I have created this select, but any error is there:

SELECT
(SELECT ROW_NUMBER() OVER (order by ORDER_ID) AS ROW_NUMBER, 
 ORDER_ID, 
 PRODUCT_NAME

FROM ORDER

How can I fix it?

Answer 1

--CREATE TABLE NAMED ORDER--

create table "order"
(
  order_id integer,
  product_name varchar2(50 char)
);

--INSERT STATEMENTS--

 insert into "order" values(007,'bicycle');
 insert into "order" values(007,'bike');
 insert into "order" values(007,'car');
 insert into "order" values(008,'car');
 insert into "order" values(008,'ship');
 insert into "order" values(009,'airplane');
 insert into "order" values(009,'rocket');
 insert into "order" values(009,'bicycle');

--QUERY--

SELECT 
RANK() OVER ( PARTITION BY order_id ORDER BY PRODUCT_NAME ASC ) AS "Row_Number",
ORDER_ID, 
PRODUCT_NAME     
FROM "order"

--RESULT SET--

Row_Number  ORDER_ID    PRODUCT_NAME
1   7   bicycle
2   7   bike
3   7   car
1   8   car
2   8   ship
1   9   airplane
2   9   bicycle
3   9   rocket