SQL Tutorial 6 Rank & Dense Rank Function

Offset Clause and LIMIT Clause uses in SQL with Example?

In SQL, the OFFSET and LIMIT clauses are often used to control the number of rows returned in a query result. They are commonly employed for implementing pagination or retrieving specific subsets of data from a table.

Here is an example of how to use the OFFSET and LIMIT clauses in SQL:

Suppose you have a table named students with columns student_id, student_name, and age, and you want to retrieve a list of students, limiting the result to 5 rows and starting from the 6th row:

— Create a sample table

CREATE TABLE students (

student_id INT PRIMARY KEY,

student_name VARCHAR(50),

age INT

);

— Insert sample data into the table

INSERT INTO students (student_id, student_name, age) VALUES

(1, ‘John Doe’, 20),

(2, ‘Jane Smith’, 22),

(3, ‘Michael Johnson’, 21),

(4, ‘Emily Williams’, 20),

(5, ‘Robert Jones’, 23),

(6, ‘Emma Brown’, 22),

(7, ‘William Davis’, 21),

(8, ‘Olivia Miller’, 20),

(9, ‘Liam Wilson’, 22),

(10, ‘Sophia Wilson’, 21);

— Retrieve students with LIMIT and OFFSET

SELECT * FROM students

ORDER BY student_id

LIMIT 5 OFFSET 5;

In this example, the LIMIT clause restricts the number of rows returned to 5, and the OFFSET clause determines the starting point for the result set. By adjusting the OFFSET value, you can paginate through the result set and retrieve data in smaller chunks.

The result of the SQL query would be:

student_id student_name age

6 Emma Brown 22

7 William Davis 21

8 Olivia Miller 20

9 Liam Wilson 22

10 Sophia Wilson 21

This query returns 5 records starting from the 6th record in the table. By adjusting the OFFSET and LIMIT values, you can retrieve different subsets of the data.

Offset and limit function in SQL with example

In SQL, the OFFSET and LIMIT clauses are commonly used together to control the number of rows returned in a query result.

They are particularly useful when you want to implement pagination or retrieve a specific subset of data from a table. The OFFSET clause specifies the number of rows to skip, and the LIMIT clause specifies the maximum number of rows to return.

This combination allows you to retrieve data in chunks, which is essential for managing large datasets efficiently.

Here is an example of how to use the OFFSET and LIMIT clauses in SQL:

Suppose you have a table named customers with columns customer_id, customer_name, and email, and you want to retrieve a list of customers in batches of 5:

— Create a sample table

CREATE TABLE customers (

customer_id INT PRIMARY KEY,

customer_name VARCHAR(50),

email VARCHAR(50)

);

— Insert sample data into the table

INSERT INTO customers (customer_id, customer_name, email) VALUES

(1, ‘John Doe’, ‘john@example.com’),

(2, ‘Jane Smith’, ‘jane@example.com’),

(3, ‘Michael Johnson’, ‘michael@example.com’),

(4, ‘Emily Williams’, ’emily@example.com’),

(5, ‘Robert Jones’, ‘robert@example.com’),

(6, ‘Emma Brown’, ’emma@example.com’),

(7, ‘William Davis’, ‘william@example.com’),

(8, ‘Olivia Miller’, ‘olivia@example.com’),

(9, ‘Liam Wilson’, ‘liam@example.com’),

(10, ‘Sophia Wilson’, ‘sophia@example.com’);

— Retrieve customers in batches of 5

SELECT * FROM customers

ORDER BY customer_id

LIMIT 5 OFFSET 0; — This will return the first 5 rows

— To retrieve the next batch of 5 rows, use OFFSET 5

SELECT * FROM customers

ORDER BY customer_id

LIMIT 5 OFFSET 5; — This will return rows 6 to 10

— To retrieve the third batch of 5 rows, use OFFSET 10

SELECT * FROM customers

ORDER BY customer_id

LIMIT 5 OFFSET 10; — This will return rows 11 to 15

Rank and Dense Rank functions in SQL with example?

In SQL, the RANK() and DENSE_RANK() functions are used to assign a rank to each row within a partition of a result set.

These functions are particularly useful when you need to determine the ranking of rows based on specific criteria. The main difference between the two functions lies in how they handle ties.

Here is an example of how to use the RANK() and DENSE_RANK() functions in SQL:

Suppose you have a table named sales with columns product_id, product_name, and revenue, and you want to rank the products based on their revenue:

— Create a sample table

CREATE TABLE sales (

product_id INT PRIMARY KEY,

product_name VARCHAR(50),

revenue DECIMAL(10, 2)

);

— Insert sample data into the table

INSERT INTO sales (product_id, product_name, revenue) VALUES

(1, ‘Product A’, 1000.00),

(2, ‘Product B’, 1500.00),

(3, ‘Product C’, 1200.00),

(4, ‘Product D’, 1500.00),

(5, ‘Product E’, 900.00);

— Use RANK() function to rank products based on revenue

SELECT

product_id,

product_name,

revenue,

RANK() OVER (ORDER BY revenue DESC) as sales_rank

FROM sales;

product_id product_name revenue sales_rank

2 Product B 1500.00 1

4 Product D 1500.00 1

3 Product C 1200.00 3

1 Product A 1000.00 4

5 Product E 900.00 5

In the above example, the RANK() function is used to assign a rank to each product based on their revenue. If there are ties (i.e., two or more products with the same revenue), the RANK() function will assign the same rank to those products and leave gaps in the ranking sequence.

To demonstrate the DENSE_RANK() function, consider the following example:

— Use DENSE_RANK() function to rank products based on revenue

SELECT

product_id,

product_name,

revenue,

DENSE_RANK() OVER (ORDER BY revenue DESC) as dense_sales_rank

FROM sales;

product_id product_name revenue dense_sales_rank

2 Product B 1500.00 1

4 Product D 1500.00 1

3 Product C 1200.00 2

1 Product A 1000.00 3

5 Product E 900.00 4

In this case, the DENSE_RANK() function assigns consecutive ranks to the products based on their revenue, ensuring that there are no gaps in the ranking sequence even if there are ties in the data.

RANK function and Dense Rank function uses in SQL with example?

In SQL, the RANK() and DENSE_RANK() functions are used to assign a rank to each row within a partition of a result set, based on the values of a specified column. These functions are helpful when you need to determine the ranking of rows based on specific criteria. The main difference between the two lies in how they handle ties.

Here’s an example of how to use the RANK() and DENSE_RANK() functions in SQL:

Suppose you have a table named employees with columns employee_id, employee_name, and salary, and you want to rank the employees based on their salary:

— Create a sample table

CREATE TABLE employees (

employee_id INT PRIMARY KEY,

employee_name VARCHAR(50),

salary DECIMAL(10, 2)

);

— Insert sample data into the table

INSERT INTO employees (employee_id, employee_name, salary) VALUES

(1, ‘John Doe’, 50000.00),

(2, ‘Jane Smith’, 60000.00),

(3, ‘Michael Johnson’, 55000.00),

(4, ‘Emily Williams’, 52000.00),

(5, ‘Robert Jones’, 62000.00);

— Use RANK() function to rank employees based on salary

SELECT

employee_id,

employee_name,

salary,

RANK() OVER (ORDER BY salary DESC) as salary_rank

FROM employees;

The output would look like this:

employee_id employee_name salary salary_rank

5 Robert Jones 62000.00 1

2 Jane Smith 60000.00 2

3 Michael Johnson 55000.00 3

4 Emily Williams 52000.00 4

1 John Doe 50000.00 5

In the above example, the RANK() function assigns a rank to each employee based on their salary. If there are ties (i.e., two or more employees with the same salary), the RANK() function will assign the same rank to those employees and leave gaps in the ranking sequence.

To demonstrate the DENSE_RANK() function, consider the following example:

— Use DENSE_RANK() function to rank employees based on salary

SELECT

employee_id,

employee_name,

salary,

DENSE_RANK() OVER (ORDER BY salary DESC) as dense_salary_rank

FROM employees;

employee_id employee_name salary dense_salary_rank

5 Robert Jones 62000.00 1

2 Jane Smith 60000.00 2

3 Michael Johnson 55000.00 3

4 Emily Williams 52000.00 4

1 John Doe 50000.00 5

In this case, the DENSE_RANK() function assigns consecutive ranks to the employees based on their salary, ensuring that there are no gaps in the ranking sequence, even if there are ties in the data.