Nested SELECT with a WHERE clause in Spark

1
votes

I have a problem with running a Spark SQL query which uses a nested select with a "where in" clause. In the query below table1 represents a temporary table which comes from a more complicated query. In the end I want to substitute table1 with this query.

select * from (select * from table1) as table2 
where (product, price) 
in (select product, min(price) from table2 group by product)

The Spark error I get says:

AnalysisException: 'Table or view not found: table2;

How could I possibly change the query to make it work as intended?

1
sqlapache-spark

1 Answers

0
votes

subquery (i.e. (select * from table1) as table2 ) is not needed & it is limited to immediate use after subquery defined you can't use with in or where clause, you can use correlated subquery instead : 

select t1.*
from table1 t1 
where t1.price = (select min(t2.price) from table1 t2 where t2.product = t1.product);