CODEWITHSUNDEEP
mysqlexasolutionexasol
xkeecs
xkeecs

Reputation: 25

Correlated subquery in Exasol

I try to write a subquery using SQL in Exasol database. The problem is similar to this thread (SQL Query - join on less than or equal date) and the code works well in mysql and postgres. However, when I move the code to Exasol, it says SQL Error 42000: correlation in on clause. I wonder if there's any alternative solution to this problem or how could i fix it in Exasol? 

    SELECT a.ID, 
           a.join_date,
           a.country, 
           a.email,
           b.start_date,
           b.joined_from
      FROM a 
 LEFT JOIN b 
        ON a.country = b.country 
       AND b.start_date = (
           SELECT MAX(start_date) 
             FROM b b2 
            WHERE b2.country = a.country 
              AND b2.start_date <= a.join_date
           );

Upvotes: 0

Views: 924

Answers (1)

Eralper
Eralper

Reputation: 6612

Although correlated queries are not supported on Exasol, it is possible to solve the requirement using DENSE_RANK() SQL function as follows

with cte as (
        select
               a.ID, a.join_date, a.country, a.email, b.start_date, b.joined_from,
               dense_rank() over (partition by b.country order by b.start_date desc) r1
        from a
        left join b
                on a.country = b.country
)
select * from cte where r1 = 1

Upvotes: 0

Related Questions