By Position

For each group in a column, we wish to filter rows by their position (row number) in the group.

This section is organized to cover the most common "filtering groups by position" scenarios as follows:

  • First where we cover how to return the first row from each group.
  • Last where we cover how to return the last row from each group.
  • nth where we cover how to return the nth row from each group.
  • Head where we cover how to return the topmost rows, defined by a number of rows or a proportion of rows, from each group.
  • Tail where we cover how to return the bottom most rows, defined by a number of rows or a proportion of rows, from each group.
  • Range where we cover how to return a range of rows, defined by a start and an end position, from each group.
  • List where we cover how to return specific rows from each group.
  • Random where we cover how to return some random rows from each group.

First

We wish to return the first row from each group where a group is defined by a given column.

In this example, we wish to return the first row of each group where the groups are defined by the column col_10 and the rows are sorted by the column col_1.

SELECT *
FROM refcon.dataset.table_1
    QUALIFY ROW_NUMBER() OVER (PARTITION BY col_10 ORDER BY col_1) = 1;

Here is how this works:

  • ROW_NUMBER() returns the index of the current row starting at 1 for the top most row.
  • We use PARTITION BY col_10 to calculate the index per group, and we order by col_1 so the index starts at 1 for the row with the lowest value for col_1.
  • We use QUALIFY clause to filter based on the output of ROW_NUMBER().
  • ROW_NUMBER() = 1 is TRUE for the first row of each group.

Last

We wish to return the last row from each group.

In this example, we wish to return the last row of each group where the groups are defined by the column col_10 and the rows are sorted by the column col_1.

SELECT *
FROM refcon.dataset.table_1
    QUALIFY ROW_NUMBER() OVER (PARTITION BY col_10 ORDER BY col_1 DESC ) = 1;

Here is how this works:

  • The code works similarly to the code in “First” scenario above except that we order by col_1 in a descending manner.

nth

We wish to return the nth row from each group.

From Top

In this example, we wish to return the second row of each group where the groups are defined by the column col_10 and the rows are sorted by the column col_1.

SELECT *
FROM refcon.dataset.table_1
    QUALIFY ROW_NUMBER() OVER (PARTITION BY col_10 ORDER BY col_1) = 2;

Here is how this works:

  • The code works similarly to the code in “First” scenario above except we filter by second row (or whatever the row position of the row we are interested in is).

From Bottom

In this example, we wish to return the row before the last of each group where the groups are defined by the column col_10 and the rows are sorted by the column col_1.

SELECT *
FROM refcon.dataset.table_1
    QUALIFY ROW_NUMBER() OVER (PARTITION BY col_10 ORDER BY col_1 DESC ) = 2;

Here is how this works:

  • The code works similarly to the code in “Last” scenario above except we filter by second row (or whatever the row position of the row relative to the end we are interested in is).

We wish to return the top n rows from each group (which are often referred to as the head).

Count

We wish to return a specific number of rows from the top of each group.

In this example, we wish to return the top two rows from each group where the groups are defined by the column col_10 and the rows are sorted by the column col_1.

SELECT *
FROM refcon.dataset.table_1
    QUALIFY ROW_NUMBER() OVER (PARTITION BY col_10 ORDER BY col_1 ) <= 2;

Here is how this works:

  • This works similarly to the “First” scenario above except that we keep the first two rows.

Proportion

We wish to return a proportion (percent) of the total number of rows from each group taken from the top of the group.

In this example, we wish to return the top 20% of the rows of each group where the groups are defined by the column col_10 and the rows are sorted by the column col_1.

SELECT *
FROM refcon.dataset.table_1
    QUALIFY ROW_NUMBER() OVER (PARTITION BY col_10 ORDER BY col_1 ) < (COUNT(1) OVER (PARTITION BY col_10) * 0.2);

Here is how this works:

  • ROW_NUMBER() works similarly to the scenarios above.
  • We count the rows in each group using COUNT(1) OVER (PARTITION BY col_10).
  • We keep rows with an index < then 20% * row_count in each group.

Tail

SELECT *
FROM refcon.dataset.table_1
    QUALIFY ROW_NUMBER() OVER (PARTITION BY col_10 ORDER BY col_1 DESC ) <= 2;

Here is how this works:

  • The code works similarly to the code in “Head” scenario above except that we order on col_1 in descending manner.

Range

We wish to return a range of rows (also known as a slice), between a given start and end row positions, from each group.

In this example, we wish to return the second through to the ninth rows from each group where the groups are defined by the column col_10 and the rows are sorted by the column col_1.

SELECT *
FROM refcon.dataset.table_1
    QUALIFY ROW_NUMBER() OVER (PARTITION BY col_10 ORDER BY col_1 ) BETWEEN 2 AND 9;

Here is how this works:

  • This works similarly to the “First” scenario above except that we filter based on a range using BETWEEN.

List

We wish to obtain specific rows, given their row numbers, from each group.

In this example, we wish to return the first, second, second last, and last rows of each group of the table df where the groups are defined by the column col_10 and the rows are sorted by the column col_1.

WITH ranked_table AS (SELECT *,
                             ROW_NUMBER() OVER (PARTITION BY col_10 ORDER BY col_1 )     row_index,
                             ROW_NUMBER() OVER (PARTITION BY col_10 ORDER BY col_1 DESC) row_index_desc,
                      FROM refcon.dataset.table_1)

SELECT * EXCEPT (row_index,row_index_desc)
FROM ranked_table
WHERE row_index IN (1, 2)
   OR row_index_desc IN (1, 2)

Here is how this works:

  • We calculate row_index and row_index_desc similarly to the above scenarios in a CTE.
  • We filter from each index separately to pick the first, second, second last, and last rows.

Random

We wish to return a set of rows taken at random from each group.

Count

We wish to return a specific number of rows taken at random positions from each group.

In this example, we wish to return 10 randomly selected rows from each group where the groups are defined by the column col_10.

SELECT *
FROM refcon.dataset.table_1
    QUALIFY ROW_NUMBER() OVER (PARTITION BY col_10 ORDER BY RAND() ) <= 10

Here is how this works:

  • This works similarly to the above scenarios except we sort by RAND() instead of a column.
  • RAND() generates a random number between 0 and 1 for each row in the table.
  • The query might return different results each time.

Proportion

We wish to return a proportion (percent) of the total number of rows from each group taken at random positions.

In this example, we wish to return 20% of the rows of each group taken at random positions where the groups are defined by the column col_10.

SELECT *
FROM refcon.dataset.table_1
    QUALIFY ROW_NUMBER() OVER (PARTITION BY col_10 ORDER BY RAND() ) < COUNT(1) OVER (PARTITION BY col_10) * 0.2;

Here is how this works:

  • This works similarly to the “Count” scenario except that we use the filter similarly to the Proportion scenario above.
SQL
I/O