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: 
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;

```
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