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.

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.

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.

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.

```
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.

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`

.

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.

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