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.

I think `row_number` is the way to go. What problems do you face when you try this approach? — Piotr Findeisen, Jun 29 '18 at 10:12
example SQL would be help to let us understand what you want to do and what's the actual results. `row_number()` is just one out 6 ranking functions — shawnzhu, Jul 02 '18 at 14:05
I'm looking for something like `SELECT* from table WHERE row_number() between 5 and 10`. But this doesn't work in Athena. — Ajjit Narayanan, Jul 02 '18 at 14:28

score 7 · Answer 1 · answered Apr 13 '20 at 10:21

7

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

answered Apr 13 '20 at 10:21

woshitom

4,811
8
38
62

But this only returns the row numbers itself not the data of your_table filtered by the row number! – leon22 Jan 11 '22 at 13:27

score 5 · Accepted Answer · answered Jul 02 '18 at 14:35

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.

Select rows by index in Amazon Athena

2 Answers2