How to return the nth row of a table in SQL on Snowflake data?
- How-Tos FAQs
- December 17, 2018
Returning the nth row of a table in SQL
We have a couple of ways to get the desired row of a table in SQL.
- A No code solution with Datameer on Snowflake
- ROW_NUMBER (Window Function)
- LIMIT… OFFSET… Clause
Let us consider the below table:
CREATE TABLE student (
student_id INT ,
student_name VARCHAR(50),
major VARCHAR(50),
batch INT
);
INSERT INTO student(student_id, student_name, major, batch)
VALUES (2, 'Dave', 'Medicine', 2017);
INSERT INTO student(student_id, student_name, major, batch)
VALUES (100, 'Jack', 'Arts', 2010);
INSERT INTO student(student_id, student_name, major, batch)
VALUES (12, 'Rose', 'Computer', 2012);
INSERT INTO student(student_id, student_name, major, batch)
VALUES (44, 'John', 'Economics', 2018);
INSERT INTO student(student_id, student_name, major, batch)
VALUES (55, 'Joe', 'Robotics', 2015);1. Keeping Track of your nth row transformations With Datameer(In Snowflake)
Datameer is a collaborative, multi-persona data transformation platform integrated into Snowflake.
With Datameer on Snowflake, you can not only return the nth value within an ordered group of values but also keep track of all your nth value transformations and their results sets.
To reap the benefits of these easy drag-and-drop modeling and self-documenting features, kickstart your snowflake instance and connect your Datameer account.
2. ROW_NUMBER (Window Function)
ROW_NUMBER (Window Function) is a standard way of selecting the nth row of a table. It is supported by all the major databases like MySQL, SQL Server, Oracle, PostgreSQL, SQLite, etc.
-- This query runs fine in both MySQL and SQL Server
-- Implementation ROW_NUMBER to returns 5th row of the table
SELECT *
FROM (
SELECT ROW_NUMBER() OVER (ORDER BY student_id) AS row_num
, student_id
, student_name
, major
, batch
FROM student
) AS sub
WHERE row_num = 5
-- For Oracle database, just remove the alias of the subquery, syntax of window function is same
SELECT *
FROM (
SELECT ROW_NUMBER() OVER (ORDER BY student_id) AS row_num
, student_id
, student_name
, major
, batch
FROM student
) -- AS sub
WHERE row_num = 5
--Output
row_num student_id student_name major batch
----------------------------------------------------
5 100 Jack Arts 20103. LIMIT… OFFSET … Clause
This is a database-specific implementation like in database MySQL, PostgreSQL. So, this will not work for every database.
-- Implementation of the LIMIT… OFFSET… clause in MySQL to return 5th row of the table
-- Here, LIMIT 1 suggests to return only one row from the query
-- And, OFFSET 4 suggests to discard top 4 rows.
-- So, the combination of LIMIT 1 OFFSET 4 will effectively return only 5th row
SELECT
student_id
, student_name
, major
, batch
FROM student
ORDER By student_id
LIMIT 1 OFFSET 4
-- Output
# student_id student_name major batch
--------------------------------------------------------------
100 Jack Arts 2010Up Next:
Read How to UPDATE a table with data from another table in SQL?
