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

Question

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)

Answer 1

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;

Answer 2

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