Oracle SQL: How to add a col to assign a seq no. but only 1 and 2 (per unique id)-CodePudding

Is there a way to assign a value ("IdNo") sequentially, 1 and 2, per CustNo? Below is my query (or I can put this query in a view), but not sure how to add an "IdNo" column that will just assign a "1" and "2" respectively, per unique CustNo.

 with row as
(select z.*,
        row_number ()
            over (partition by CustNo
                  order by FoodDate desc)    rn
   from table z)
  select CustNo,
         Food,
         FoodDate   
    from row
   where rn <= 2
order by CustNo, FoodDate desc

Below is what I want it to look like with the IdNo added...

IdNo    CustNo    Food                FoodDate
1       101       Red-Apple           7/5/22
2       101       Red-Apple           7/5/22
1       256       Red-Apple           7/11/22
2       256       Red-Cherry          5/20/22
1       418       Blue-Muffin         4/1/22 
2       418       Blue-Berry          3/16/22
1       599       Orange-Persimmon    2/8/22
2       599       Red-Apple           1/23/22
1       654       Blue-Berry          12/4/21
2       654       Yellow-Banana       11/27/21

CodePudding user response：

Just add rn on your query.

with row as
(select z.*,
        row_number ()
            over (partition by CustNo
                  order by FoodDate desc)    rn
   from table z)
  select rn as IdNo,
         CustNo,
         Food,
         FoodDate   
    from row
   where rn <= 2
order by CustNo, FoodDate desc