Select rows by index in Amazon Athena

Question

This is a very simple question but I can't seem to find documentation on it. How one would query rows by index (ie select the 10th through 20th row in a table)?

I know there's a row_numbers function but it doesn't seem to do what I want.

Answer 1

Do not specify any partition so your row number will be an integer between 1 and your number of record.

SELECT  row_num FROM (
  SELECT row_number() over () as row_num
FROM your_table 
  )
  WHERE row_num between 100000 and 100010

Answer 2

I seem to have found a roundabout and clunky way of doing this in Athena, so any better answers are welcome. This approach requires you have some numeric column in your table already, in this case named some_numeric_column:

SELECT some_numeric_column, row_num FROM (
  SELECT some_numeric_column,
row_number() over (order by some_numeric_column) as row_num
FROM your_table 
  )
  WHERE row_num between 100000 and 100010

To explain, you first select some numeric column in your data, then create a column (called row_num) of row numbers which is based on the order of your selected numeric column. Then you wrap that all in a select call because Athena doesn't support creating and then conditioning on the row_num column within a single call. If you don't wrap it in a second SELECT call Athena will spit out some errors about not finding a column named row_num.