Midhun Siva Midhun Siva - 20 days ago 7
SQL Question

How to achive this without using Sub Query, CTE and Prodedure

I have a table with 2 fields

CREATE TABLE Temp_tab
(
id int identity primary key,
value float
);

INSERT INTO Temp_tab(value)
VALUES (65.09),(17.09);


I want to select all the records that are greater than Avg(Value).

Say...
Select * from temp_tab where value > (select avg(value) from temp_tab);

This above query(using subquery) gives me the expected output

1 65.09


I want to achieve this without using Sub Query, CTE and Prodedure, since i am using Spark DB. Spark Db does not support Sub Queries, CTE and Prodedures

Answer

You can do this quite painfully with a cross join and aggregation:

Select t1.id, t1.value
from temp_tab t1 cross join
     temp_tab t2
group by t1.id, t1.value
having t1.value > avg(t2.value);

As a note: Spark SQL claims to support subqueries (see here). So, your original query should work. If it only supports subqueries in the from clause, then you can do:

Select t.*
from temp_tab t join
     (select avg(value) as avgvalue from temp_tab) a
     on t.value > a.avgvalue;
Comments