Skip to main content

Correlated subquery with distinct/group by is not supported

  • March 15, 2023
  • 1 reply
  • 4 views
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.

    1 reply

    VValdar
    Forum|alt.badge.img+1
    • VValdar
    • Participating Frequently
    • March 15, 2023

    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

    Still scrolling?
    Ask a question
    Terms & ConditionsAccessibility statement