How to Get First Row of each Group in Snowflake?

  • Post author:
  • Post last modified:June 9, 2021
  • Post category:Snowflake
  • Reading time:6 mins read

In the data warehouse reporting, you will encounter many different scenarios. One of such scenario is to get first row of each group. For example, identify the department wise highest salary. In this article, we will check how to select or get first row of each group in Snowflake.

Select First Record of each Group in Snowflake

Selecting first row of each group in SQL is one of the common query in reporting. You need to use proper function that does not take much time to return results.

Following are a couple of methods that you can use to select or get first record of each group in Snowflake.

Let us check these two methods briefly.

Test Data

Following employee table will be used in subsequent examples.

CREATE OR REPLACE TABLE employee AS
SELECT *
FROM 
VALUES ('James','Sales',3000),
      ('Michael','Sales',4600),
      ('Robert','Sales',4100),
      ('Maria','Finance',3000),
      ('Raman','Finance',3000),
      ('Scott','Finance',3300),
      ('Jen','Finance',3900),
      ('Jeff','Marketing',3000),
      ('Kumar','Marketing',2000) AS dept(NAME,DEPT,SALARY);

Snowflake Row_number Window Function to Select First Row of each Group

Firstly, we will check on row_number() window function. The row_number window function returns a unique row number for each row within a window partition. The row number starts at 1 and continues up sequentially.

Consider following example in which we are partitioning data on the department column which groups all same departments into a group and then apply order on salary column. The resulting output would be department wise highest salary.

SELECT NAME,DEPT,SALARY
FROM (SELECT *, ROW_NUMBER () OVER(PARTITION BY DEPT ORDER BY SALARY DESC) AS rn
FROM employee
) WHERE rn = 1;

Following is the output.

+---------+-----------+--------+
| NAME    | DEPT      | SALARY |
|---------+-----------+--------|
| Jeff    | Marketing |   3000 |
| Jen     | Finance   |   3900 |
| Michael | Sales     |   4600 |
+---------+-----------+--------+

Snowflake FIRST_VALUE Window Function to Select First Row of each Group

Secondly, we will check FIRST_VALUE Window Function. The first_value function returns the first value within an ordered group of values.

Consider following example in which we are partitioning data on the department column which groups all same departments into a group and then apply order on salary column. The first_value function will pick first salary within the group.

SELECT NAME, DEPT, FIRST_VALUE(SALARY) OVER(PARTITION BY DEPT ORDER BY SALARY DESC) AS SALARY
FROM employee;

Following is the output.

+---------+-----------+--------+
| NAME    | DEPT      | SALARY |
|---------+-----------+--------|
| James   | Sales     |   4600 |
| Michael | Sales     |   4600 |
| Robert  | Sales     |   4600 |
| Maria   | Finance   |   3900 |
| Raman   | Finance   |   3900 |
| Scott   | Finance   |   3900 |
| Jen     | Finance   |   3900 |
| Jeff    | Marketing |   3000 |
| Kumar   | Marketing |   3000 |
+---------+-----------+--------+

Snowflake NTH_VALUE Window Function to Select nth Row of each Group

Thirdly, we will check NTH_VALUE Window Function. The NTH_VALUE Window Function returns the nth value (up to 1000) within an ordered group of values. This is a special function which allows you to select any particular row within a group. For example, you can use the Snowflake nth_value function to select second or third rows within a group.

Consider following example to select first row using nth_value function.

SELECT NAME, DEPT, NTH_VALUE(SALARY, 1) OVER(PARTITION BY DEPT ORDER BY SALARY DESC) AS SALARY
FROM employee;

Following is the output.

+---------+-----------+--------+
| NAME    | DEPT      | SALARY |
|---------+-----------+--------|
| James   | Sales     |   4600 |
| Michael | Sales     |   4600 |
| Robert  | Sales     |   4600 |
| Maria   | Finance   |   3900 |
| Raman   | Finance   |   3900 |
| Scott   | Finance   |   3900 |
| Jen     | Finance   |   3900 |
| Jeff    | Marketing |   3000 |
| Kumar   | Marketing |   3000 |
+---------+-----------+--------+
Snowflake NTH_VALUE Window Function to Select Second Row of each Group

Following example will use the nth_value function to select second row of each group.

SELECT NAME, DEPT, NTH_VALUE(SALARY, 2) OVER(PARTITION BY DEPT ORDER BY SALARY DESC) AS SALARY
FROM employee;

and the output is

+---------+-----------+--------+
| NAME    | DEPT      | SALARY |
|---------+-----------+--------|
| James   | Sales     |   4100 |
| Michael | Sales     |   4100 |
| Robert  | Sales     |   4100 |
| Maria   | Finance   |   3300 |
| Raman   | Finance   |   3300 |
| Scott   | Finance   |   3300 |
| Jen     | Finance   |   3300 |
| Jeff    | Marketing |   2000 |
| Kumar   | Marketing |   2000 |
+---------+-----------+--------+

Related Articles,

Hope this helps 🙂

Tags: , ,