CODEWITHSUNDEEP
sqlgoogle-bigquery
Jazz
Jazz

Reputation: 475

BIGQUERY - LEFT OUTER JOIN cannot be used without a condition that is an equality of fields from both sides of the join

I am trying to find 5th highest salary in bigquery using this query but it gives me error

LEFT OUTER JOIN cannot be used without a condition that is an equality of fields from both sides of the join.

I believe this is the right query for sql for this question but something is not working out in bigquery. Can anybody help me with this? :)

select  concat(first_name, ' ', last_name) as Name, salary
from `table` w1
where 4 = (select count(distinct(salary))
 from  `table` w2
 where w2.salary > w1.salary)

Upvotes: 1

Views: 789

Answers (2)

Mikhail Berlyant
Mikhail Berlyant

Reputation: 172993

Below is for BigQuery Standard SQL

Using functions like DENSE_RANK(), ROW_NUMBER() and such for big volumes of data usually ends up with some thing like Resource Limits Exceeded error.

Depends on your real use case - you can consider below alternatives: 

#standardSQL
SELECT *
FROM `project.dataset.table`
ORDER BY salary DESC
LIMIT 1 OFFSET 4

OR 

#standardSQL
SELECT AS VALUE ARRAY_AGG(t ORDER BY salary DESC LIMIT 5)[SAFE_OFFSET(4)]
FROM `project.dataset.table` t

Both above versions should give you a record with 5th highest salary

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269853

Your query appears to be returning rows that have four larger salaries. That would be the fifth largest salary. So, just use dense_rank():

select w.*
from (select w.*,
             dense_rank() over (order by salary desc) as seqnum
      from `table` w
     ) w
where seqnum = 5;

Upvotes: 2

Related Questions