How to get Top 10 or N Rows in Snowflake

· 650 words · 4 minute read

How to Get the Top 10 Rows from a Table in Snowflake 🔗

Objective 🔗

Learn how to retrieve the top 10 or top n rows from a Snowflake table.

Why Retrieve Top Rows? 🔗

Retrieving top rows from a table is a common task in data analysis. For example, you might want to identify the top 10 highest-paid employees, the top 10 best-selling products, or the top 10 students with the highest scores. In this tutorial, we’ll explore how to use the LIMIT clause in Snowflake to achieve this.

Creating a Table in Snowflake 🔗

Before we dive into the syntax, let’s create a table in Snowflake. We’ll create a table called students with columns id, name, grade, and score.

CREATE TABLE students (
  id INT,
  name VARCHAR(50),
  grade VARCHAR(10),
  score INT
);

Inserting Data into the Table 🔗

Next, let’s insert some sample data into the students table.

INSERT INTO students (id, name, grade, score)
VALUES
  (1, 'John Doe', 'A', 90),
  (2, 'Jane Smith', 'B', 85),
  (3, 'Bob Johnson', 'A', 95),
  (4, 'Alice Brown', 'C', 75),
  (5, 'Mike Davis', 'B', 80),
  (6, 'Emily Taylor', 'A', 92),
  (7, 'Sarah Lee', 'C', 70),
  (8, 'Kevin White', 'B', 88),
  (9, 'Lisa Lee', 'A', 98),
  (10, 'Tom Harris', 'B', 82),
  (11, 'Emma Clark', 'A', 93),
  (12, 'James Lewis', 'C', 72),
  (13, 'Olivia Martin', 'B', 86),
  (14, 'Sophia Walker', 'A', 94),
  (15, 'Daniel Hall', 'C', 78);
ID Name Grade Score
1 John Doe A 90
2 Jane Smith B 85
3 Bob Johnson A 95
4 Alice Brown C 75
5 Mike Davis B 80
6 Emily Taylor A 92
7 Sarah Lee C 70
8 Kevin White B 88
9 Lisa Lee A 98
10 Tom Harris B 82
11 Emma Clark A 93
12 James Lewis C 72
13 Olivia Martin B 86
14 Sophia Walker A 94
15 Daniel Hall C 78

Retrieving Top Rows 🔗

Now, let’s learn how to retrieve the top 10 rows from the students table using the LIMIT clause. The syntax is as follows:

SELECT column1, column2, ...
FROM tablename
ORDER BY column_to_order_by
LIMIT 10;

Explanation:

  • SELECT column1, column2, ...: This clause specifies the columns we want to retrieve from the table. You can choose one or more columns.
  • FROM tablename: This clause specifies the table from which we want to retrieve the data.
  • ORDER BY column_to_order_by: This clause sorts the data in ascending or descending order based on the specified column. You can choose any column to order by.
  • LIMIT 10: This clause limits the number of rows returned to 10.

Example 🔗

Let’s assume we want to retrieve the top 10 students with the highest scores.

SELECT name, score
FROM students
ORDER BY score DESC
LIMIT 10;

This query will return the top 10 students with the highest scores, along with their names and scores.

Name Score
Lisa Lee 98
Bob Johnson 95
Sophia Walker 94
Emma Clark 93
Emily Taylor 92
John Doe 90
Kevin White 88
Olivia Martin 86
Jane Smith 85
Tom Harris 82

Exercises 🔗

  1. Write a SQL query to select the first 5 students from the table.
-- Your answer here
  1. Write a SQL query to select the top 3 students based on their scores in descending order.
-- Your answer here
  1. Write a SQL query to select the names and scores of the bottom 4 students based on their scores in ascending order.
-- Your answer here

Solutions

  1. Write a SQL query to select the first 5 students from the table.
SELECT *
FROM students
ORDER BY score DESC
LIMIT 3;
  1. Write a SQL query to select the top 3 students based on their scores in descending order.
SELECT name, score
FROM students
ORDER BY score ASC
LIMIT 4;
  1. Write a SQL query to select the names and scores of the bottom 4 students based on their scores in ascending order.
SELECT name, grade
FROM students
ORDER BY score DESC, name ASC
LIMIT 6;
Snowflake