How do I limit the number of rows per id on an oracle database?

I want to store the last 20 products a user has looked at. If the user browses to product number 21 the oldest of the records should be deleted from the ORACLE database.

The table contains the recently viewed products of all the users in the system.

So basically I need to check if user X has over Y records in a table and if so delete the oldest records so that user X only ever has Y records in the recently viewed products table.

Any help would be appreciated.

Here is an attempt I have made which does not work correctly.

DELETE FROM VIEWED_PRODUCT
WHERE id NOT IN (
  SELECT id
  FROM (
    SELECT * FROM (
      SELECT id
      FROM VIEWED_PRODUCT
      WHERE MY开发者_开发知识库_ID = 1626
      ORDER BY CREATED_AT DESC )
        WHERE ROWNUM <= 20 
  ) z
);

Thanks.

---

You can use analytic functions like this:

delete viewed_product where id in
( select id from
  ( select id, row_number() over (order by created_at desc) rn
    from viewed_product
    where my_id = :bindvar
  )
  where rn > 20
)