SQL & PL/SQL

Hi all,

I have a query that's being converted from Postgres to Oracle SQL. Included is a row_id value that uses row_number() over (partition by [multiple columns] order by [column]. The last query partition clause in Postgres is a not [column] is null.

I've created an example in Postgres to illustrate this:

As I understand it, the not [column] is null specifies that the grouping is split up between null and not-null groups for the specified field, each with their own rankings. As you can see above, the rankings above give independent values for those values that do have or do not have a value for third_id.

My question is: Is there any query partition clause in the Oracle partition by that would return identical results? I've searched and looked through the Analytic Functions documentation but haven't found any. We're using Oracle 19c (19.18.0.0.0) and SQL Developer.

I was able to reproduce the results in Oracle by dividing the query into null and non-null instances for third_id and unioning the results back together, but I was wondering if there's a better approach.

Included below are the create table / insert statements and query for Oracle.

Thanks in advance!

Chris

CREATE TABLE TEST_TABLE
   ("ID" NUMBER, 
    "SECOND_ID" NUMBER, 
    "THIRD_ID" NUMBER, 
    "TIMESTAMPVAL" TIMESTAMP (6)
   );

INSERT INTO TEST_TABLE (ID, SECOND_ID, THIRD_ID, TIMESTAMPVAL) VALUES (1, 1, NULL, TO_TIMESTAMP('2020-10-01 13:56:30', 'YYYY-MM-DD HH24:MI:SS'));
INSERT INTO TEST_TABLE (ID, SECOND_ID, THIRD_ID, TIMESTAMPVAL) VALUES (1, 1, NULL, TO_TIMESTAMP('2021-02-01 02:58:45', 'YYYY-MM-DD HH24:MI:SS'));
INSERT INTO TEST_TABLE (ID, SECOND_ID, THIRD_ID, TIMESTAMPVAL) VALUES (1, 1, 1, TO_TIMESTAMP('2020-10-01 14:52:20', 'YYYY-MM-DD HH24:MI:SS'));
INSERT INTO TEST_TABLE (ID, SECOND_ID, THIRD_ID, TIMESTAMPVAL) VALUES (1, 1, 2, TO_TIMESTAMP('2020-10-01 16:59:10', 'YYYY-MM-DD HH24:MI:SS'));
INSERT INTO TEST_TABLE (ID, SECOND_ID, THIRD_ID, TIMESTAMPVAL) VALUES (1, 2, NULL, TO_TIMESTAMP('2021-01-07 22:11:30', 'YYYY-MM-DD HH24:MI:SS'));
INSERT INTO TEST_TABLE (ID, SECOND_ID, THIRD_ID, TIMESTAMPVAL) VALUES (1, 2, NULL, TO_TIMESTAMP('2021-02-03 05:01:45', 'YYYY-MM-DD HH24:MI:SS'));
INSERT INTO TEST_TABLE (ID, SECOND_ID, THIRD_ID, TIMESTAMPVAL) VALUES (1, 2, 1, TO_TIMESTAMP('2021-07-01 16:16:20', 'YYYY-MM-DD HH24:MI:SS'));
INSERT INTO TEST_TABLE (ID, SECOND_ID, THIRD_ID, TIMESTAMPVAL) VALUES (1, 2, 2, TO_TIMESTAMP('2020-03-07 17:57:10', 'YYYY-MM-DD HH24:MI:SS'));
INSERT INTO TEST_TABLE (ID, SECOND_ID, THIRD_ID, TIMESTAMPVAL) VALUES (2, 1, NULL, TO_TIMESTAMP('2022-10-01 13:56:30', 'YYYY-MM-DD HH24:MI:SS'));
INSERT INTO TEST_TABLE (ID, SECOND_ID, THIRD_ID, TIMESTAMPVAL) VALUES (2, 1, NULL, TO_TIMESTAMP('2023-02-01 02:58:45', 'YYYY-MM-DD HH24:MI:SS'));
INSERT INTO TEST_TABLE (ID, SECOND_ID, THIRD_ID, TIMESTAMPVAL) VALUES (2, 1, 1, TO_TIMESTAMP('2022-10-01 14:52:20', 'YYYY-MM-DD HH24:MI:SS'));
INSERT INTO TEST_TABLE (ID, SECOND_ID, THIRD_ID, TIMESTAMPVAL) VALUES (2, 1, 2, TO_TIMESTAMP('2022-10-01 16:59:10', 'YYYY-MM-DD HH24:MI:SS'));
INSERT INTO TEST_TABLE (ID, SECOND_ID, THIRD_ID, TIMESTAMPVAL) VALUES (2, 2, NULL, TO_TIMESTAMP('2023-01-07 22:11:30', 'YYYY-MM-DD HH24:MI:SS'));
INSERT INTO TEST_TABLE (ID, SECOND_ID, THIRD_ID, TIMESTAMPVAL) VALUES (2, 2, NULL, TO_TIMESTAMP('2023-02-03 05:01:45', 'YYYY-MM-DD HH24:MI:SS'));
INSERT INTO TEST_TABLE (ID, SECOND_ID, THIRD_ID, TIMESTAMPVAL) VALUES (2, 2, 1, TO_TIMESTAMP('2023-07-01 16:16:20', 'YYYY-MM-DD HH24:MI:SS'));
INSERT INTO TEST_TABLE (ID, SECOND_ID, THIRD_ID, TIMESTAMPVAL) VALUES (2, 2, 2, TO_TIMESTAMP('2022-03-07 17:57:10', 'YYYY-MM-DD HH24:MI:SS'));

SELECT 
    id, 
    second_id, 
    third_id,
    timestampval,
    row_number () over (partition by id, second_id order by timestampval asc) as row_id
  FROM test_table
  where third_id is not null
  union all
SELECT 
    id, 
    second_id, 
    third_id,
    timestampval,
    row_number () over (partition by id, second_id order by timestampval asc) as row_id
  FROM test_table
  where third_id is null;