Postgres Conversion of ORacle KEEP DENSE_RANK FIRST

Question

Converting a particular system form Oracle to Postgres where I was using the Oracle KEEP dense rank first function. In that system (for various reasons) the update statement must be formatted like this:

UPDATE aaa a
   SET a.dataval =
           (SELECT MAX (b.data_val) KEEP (DENSE_RANK FIRST ORDER BY b.sort_val)
              FROM bbb b
             WHERE a.id = b.aaa_id);

Or for testing we can write it as an inline view fashion for Oracle:

SELECT id,
       (SELECT MAX (b.data_val) KEEP (DENSE_RANK FIRST ORDER BY b.sort_val)
          FROM bbb b
         WHERE a.id = b.aaa_id)
  FROM aaa a;

So I tried a similar construct in Postgres like this along with many other tries with window and row ranges etc.

select  id,
    (select first_value(b.data_val) over (order by b.sort_val)
       from bbb b
      where a.id = b.aaa_id )
from aaa a;

and I get the error: SQL Error [21000]: ERROR: more than one row returned by a subquery used as an expression.

I was expecting this to return one row from the bbb table for each row of aaa. But that doesn't seem to happen. Is there a way to format the query in this way for Postgres? Any help greatly appreciated for Postrges novice.

Here is a script to create test objects in Oracle.

CREATE TABLE aaa (
    id integer NOT NULL,
    dataval integer NULL
);

CREATE TABLE bbb (
    aaa_id integer NOT NULL,
    bbb_id integer NOT NULL,
    sort_val integer NOT NULL,
    data_val integer NOT NULL
);

INSERT INTO aaa (id,dataval) VALUES (1,NULL);
INSERT INTO aaa (id,dataval) VALUES (2,NULL);
INSERT INTO aaa (id,dataval) VALUES (3,NULL);

INSERT INTO bbb (aaa_id,bbb_id,sort_val,data_val) VALUES     (1,1,3,10);
INSERT INTO bbb (aaa_id,bbb_id,sort_val,data_val) VALUES     (1,2,2,11);
INSERT INTO bbb (aaa_id,bbb_id,sort_val,data_val) VALUES     (1,3,1,12);
INSERT INTO bbb (aaa_id,bbb_id,sort_val,data_val) VALUES     (2,4,6,13);
INSERT INTO bbb (aaa_id,bbb_id,sort_val,data_val) VALUES     (2,5,5,14);
INSERT INTO bbb (aaa_id,bbb_id,sort_val,data_val) VALUES     (2,6,4,15);
INSERT INTO bbb (aaa_id,bbb_id,sort_val,data_val) VALUES     (3,7,9,16);
INSERT INTO bbb (aaa_id,bbb_id,sort_val,data_val) VALUES     (3,8,8,17);
INSERT INTO bbb (aaa_id,bbb_id,sort_val,data_val) VALUES     (3,9,7,18);

--Query
SELECT id,
       (SELECT MAX (b.data_val) KEEP (DENSE_RANK FIRST ORDER BY b.sort_val)
          FROM bbb b
         WHERE a.id = b.aaa_id)
  FROM aaa a;

/*expected Result
ID                                     DATAVAL                                 
1                                      12                                      
2                                      15                                      
3                                      18                                      
*/

Here is the same test object setup for Postgres:

CREATE TABLE aaa (
    id int8 NOT NULL,
    dataval int8 NULL
);
CREATE UNIQUE INDEX aaa_id_idx ON aaa (id);

CREATE TABLE bbb (
    aaa_id int8 NOT NULL,
    bbb_id int8 NOT NULL,
    sort_val int8 NOT NULL,
    data_val int8 NOT NULL
);
CREATE UNIQUE INDEX bbb_aaa_id_idx ON bbb on (aaa_id, bbb_id);

INSERT INTO aaa (id,dataval) VALUES
     (1,NULL),
     (2,NULL),
     (3,NULL);
INSERT INTO bbb (aaa_id,bbb_id,sort_val,data_val) VALUES
     (1,1,3,10),
     (1,2,2,11),
     (1,3,1,12),
     (2,4,6,13),
     (2,5,5,14),
     (2,6,4,15),
     (3,7,9,16),
     (3,8,8,17),
     (3,9,7,18);

Thanks!

user_10676716 · Answer

You can use a subquery - or a lateral join - with a row-limiting clause

select a.id, b.data_val
from aaa a
left join lateral (
    select b.data_val
    from bbb b
    where b.aaa_id = a.id
    order by b.sort_val
    limit 1
) b on true

An alternative is distinct on:

select distinct on (a.id) a.id, b.data_val
from aaa a
left join bbb b on b.aaa_id = a.id
order by a.id, b.sort_val

Demo on DB Fiddle - both queries yield:

id | data_val
-: | -------:
 1 |       12
 2 |       15
 3 |       18

If you want an update:

update aaa a
set data_val = (
    select b.data_val
    from bbb b
    where b.aaa_id = a.id
    order by sort_val
    limit 1
)

Postgres Conversion of ORacle KEEP DENSE_RANK FIRST

1 Answers