Correlated subquery with distinct/group by is not supported

VeeraK

I'm facing an issue with vertica query "Correlated subquery with distinct/group by is not supported".
Actually I'm joining the two queries into a single query and using first query result value in the second query where clause param.

Query1 - SELECT t1.ID, t1.NAME FROM Table1 t1 WHERE (t1.id ,t1.timestamp) in(select x1.id,max(x1.timestamp) from Table1 x1 WHERE x1.id = '12345' group by x1.id);

Query2 - SELECT t2.FIRST_NAME, t2.LAST_NAME FROM Table2 WHERE (t2.id ,t2.load_timestamp) in(select y1.id,max(y1.load_timestamp) from Table2 y1 WHERE y1.id = '6789 group by y1.id);

in Query1 t1.ID value I'm using in Query2 where condition. above two queries are joining like below
SELECT t1.ID, t1.NAME, t2.FIRST_NAME, t2.LAST_NAME FROM Table1 t1 LEFT JOIN Table2 t2 on t2.id = t1.ID WHERE (t1.ID ,t1.timestamp) in(select x1.ID,max(x1.timestamp) from Table1 x1 WHERE x1.ID = '12345' group by x1.ID) WHERE(t2.id ,t2.load_timestamp) in(select y1.id,max(y1.load_timestamp) from Table2 y1 WHERE y1.id = t1.ID group by y1.id);

while executing the query getting above error message Correlated subquery with distinct/group by is not supported
Can anyone please help on this issue.

VValdar

We have quite some restriction in correlated subqueries as you noticed, but we also have much more efficient way to run the query you sent.
Query1:

select id, name
  from Table1
 where id = '12345'
 limit 1 over(partition by id order by timestamp desc);

Query 2:

select first_name, last_name
  from Table2
 where id = '6789'
 limit 1  over(partition by id order by load_timestamp desc);

Query 3:

with cte_last_table1 (id, name) as
(
select id, name
  from Table1
 limit 1 over(partition by id order by timestamp desc)
)
  ,  cte_last_table2 (id, first_name, last_name) as
(
select id, first_name, last_name
  from Table2
 limit 1  over(partition by id order by load_timestamp desc)
)
    select t1.id, t1.name, t2.first_name, t2.last_name
      from cte_last_table1 as t1
 left join cte_last_table2 as t2 on t2.id = t1.id
     where t1.id = '12345';

And note you can have specific top-K projections to pre-compute the limit 1:
https://www.vertica.com/docs/12.0.x/HTML/Content/Authoring/AnalyzingData/AggregatedData/TopKProjectionsCreate.htm