Trying to create Row number for Distinct values in BigQuery

Question

On BigQuery, I'm trying to get the row count of the distinct values to display for easy reference. Assuming I have 1000 distinct values and I'm trying to get the 340th row of distinct value, how should i code it.

I tried to run

SELECT
  DISTINCT column_2
FROM
  table

and sure it turns out all the DISTINCT values of column_2. But how do i add the row number beside, and would I be able to put a WHERE for the row number?

Answer 1

Consider below approach

select distinct column_2
from your_table
qualify 340 = dense_rank() over(order by column_2)

Answer 2

Since BigQuery works parallelized there is no guarantee/need for any sorting of table rows. That also means there are no row numbers.

If you want the nth element of a query result you need to define a sorting logic beforehand. You can use navigational functions for that, or a LIMIT with OFFSET if you need one exact value

with t as (

              select 'a' as val
    union all select 'a'
    union all select 'b'
    union all select 'c'
    union all select 'c'
    union all select 'f'
    union all select 'y'
    union all select 'z'
    union all select 'a'
)


select 
    distinct val 
from t
order by 1
-- 5th element has offset 4
limit 1 offset 4