Only Subquery Expressions That Are Top Level Conjuncts Are Allowed Error
I Have the Below Sql Running in Hive, Where I Want to Use the Output of One Query and Use It to Then Return All the Records That = Value Returned. Am Getting...
I have the below sql running in hive, where I want to use the output of one query and use it to then return all the records that = value returned. Am getting an error;
Error Error while compiling statement: FAILED: SemanticException Line 0:-1 Unsupported SubQuery Expression '1': Only SubQuery expressions that are top level conjuncts are allowed
SELECT * from database1.table1
where FieldID = (
select *
FROM database1.table1
order by date/time DESC
limit 1)
The idea is that the sub query would return the highest/latest most record. Then using that record look up the same table and return all the records associated with that id.
So in the table below, you'll see it's ordered by date/time, the return on the sub query should be 8. Using the output of 8, show me all the records in the same table where FieldID = 8
Data Table
| FieldID | date/time |
|---|---|
| 8 | 17/6 1pm |
| 2 | 17/6 12pm |
| 1 | 17/6 11am |
| 2 | 17/6 10am |
| 8 | 17/6 9am |
| 4 | 17/6 8am |
| 8 | 17/6 7am |
| 1 | 17/6 6am |
| 8 | 17/6 5am |
| 8 | 17/6 4am |
1 Answer
You can use JOIN instead:
select t1.*
from database1.table1 t1 join
(select max(date_time) as max_date_time
from database1.table1
) tt1
on tt1.max_date_time = t1.date_time;
Alexander Ross has covered the video game industry for a decade, writing deep dives on game design, esports tournaments, VR developments, and gaming culture.
