Range of Rows

We wish to obtain a range of rows of a table. This is commonly referred to as slicing.

Range

We wish to get a range of rows between a given start position and end position.

SELECT *
FROM refcon.dataset.table_1
    QUALIFY ROW_NUMBER() OVER () BETWEEN 1 AND 9;

Here is how this works:

  • ROW_NUMBER is a window function that ranks the rows of the table.
  • QUALIFY is used to filter the rows based on window function results.

Specific Sort

Often times we are faced with scenarios where we need the table to be sorted in a certain way before we take a slice. In other words, We wish to sort the table by a particular column (or set of columns) and then take a slice.

SELECT *
FROM refcon.dataset.table_1
    QUALIFY ROW_NUMBER() OVER ( ORDER BY col_1) BETWEEN 1 AND 9

Here is how this works:

  • ROW_NUMBER is a window function that ranks the rows of the table.
  • ORDER BY clause decides how the rows are ranked( by ASC order of col_1 here).
  • QUALIFY is used to filter the rows based on window function results.

From End

Get a range of rows (slice) relative to the bottom of the table.

WITH 
   table_rows AS
     (SELECT row_count
      FROM refcon.dataset.__TABLES__
      WHERE table_id = 'table_1'),

  ranked_table AS
     (SELECT *
             ,ROW_NUMBER() OVER ( ) - (SELECT row_count FROM table_rows) -1
                                                        AS inverse_row_num

      FROM refcon.dataset.table_1)

SELECT * EXCEPT (inverse_row_num)
FROM ranked_table
WHERE inverse_row_num BETWEEN -9 AND -1;

Here is how it works:

  • First, we get the number of rows in the table to calculate the offset.
  • In ranked_table CTE, we get the inverse_row_num of each row by using the formula ROW_NUMBER() OVER ( ) - (SELECT row_count FROM table_rows) - 1.
  • We use BETWEEN to keep the last 10 rows.
SQL
I/O