How to stop out of order results in this query-CodePudding

I have a table like

CREATE TABLE FOO
(
  "ID" NUMBER(30,0),
  "CREATION_TIME" TIMESTAMP(6),
  "NAME" VARCHAR2(32 BYTE),
  "TRANSACTION_NUMBER" NUMBER(30,0)
);

I have a requirement to get a max of 100 entries that have a CREATION_TIME > 30 days in the past, per NAME. Also regardless of CREATION_TIME I have to return at least one entry with the highest ID. For example if all entries are older than 30 days I should return 1 entry with the highest ID. I have this query:

SELECT * FROM 
  (WITH rws AS (SELECT o.*, row_number() over (PARTITION BY NAME ODER BY ID DESC) rn
  FROM ORDERS o WHERE CREATION_TIME > sysdate-30)
  SELECT * FROM rws WHERE rn<=100)
UNION
  SELECT * FROM (WITH rws AS (SELECT o.*, row_number() over (PARTITION BY NAME ORDER BY ID DESC) RN
  FROM ORDERS o ORDER BY ID DESC)
  SELECT * FROM rws WHERE rn<=1)

The problem is there is no guarantee that IDs are increasing with time. For the most part they are, but there can be a case like:

ID, CREATION_TIME, NAME,   TRANSACTION_NUMBER
---------------------------------------------
4,  today          foo,    111
2,  29 days ago    foo,    122
3,  30 days ago    foo,    123
1,  31 days ago    foo,    133

In this case the query would incorrectly return 3 entries (ID=2,3,4) when it should return only the top two, (ID=4,2). I have tried modifying the query to fix this but I'm out of ideas. Any help is greatly appreciated. Note due to production risk I can't make changes to the database, I have to solve this with my select query. The query has to run on H2 and Oracle.

CodePudding user response：

You appear to want:

SELECT *
FROM   (
  SELECT o.*,
         ROW_NUMBER() OVER (PARTITION BY name ORDER BY creation_time DESC) AS rn,
         MAX(id) OVER (PARTITION BY name) AS max_id
  FROM   ORDERS o
)
WHERE (CREATION_TIME > sysdate-30 AND rn <= 100)
OR    (id = max_id);

db<>fiddle here