Introduction

It's important to understand the SQL ORDER BY Limit stands out as a powerful command that not only sorts data but also allows us to control the number of rows in our result set.

What is SQL ORDER BY Limit?.

SQL ORDER BY Limit is a command used to fetch rows from a table in a specified sorting order, be it ascending or descending, while also restricting the number of rows in the result set

SELECT column_name1, column_name2, ..., column_nameN
FROM table_name
[WHERE conditions]
[ORDER BY column_name ASC | DESC]
LIMIT number_of_rows [OFFSET offset_value];

• column_name1, column_name2, ..., column_nameN: Represents the column names.
• number_of_rows: Specifies the quantity of rows displayed from the first row.
• offset_value: Determines how many rows should be skipped from the first row.

Real-world Examples

Consider the table(s) below as an example to use when creating the SQL query for getting the results you want.

cus_id	cus_name	amount	lane_id
001	Customer1	45000.00	1000
002	Customer2	40000.00	2000
003	Customer3	27000.00	1000
004	Customer4	25000.00	2000
005	Customer5	20000.00	1000
006	Customer6	18000.00	1000
007	Customer7	15000.00	1000
008	Customer8	14000.00	2000
009	Customer9	13000.00	2000

1. Sorting in Descending Order

Imagine organizing a list of customer by their IDs in descending order. The query would look like this:

SELECT * FROM customer_details ORDER BY cus_id DESC LIMIT 5;

This not only fetches the top 5 customers but also arranges them in descending order of their IDs as shown in blow.

cus_id	cus_name	amount	lane_id
009	Customer9	13000.00	2000
008	Customer8	14000.00	2000
007	Customer7	15000.00	1000
006	Customer6	18000.00	1000
005	Customer5	20000.00	1000

2. Skip Rows with OFFSET

Consider a scenario where you want to skip the first two rows in the result set. The query becomes:

SELECT * FROM customer_details ORDER BY cus_id ASC LIMIT 5 OFFSET 2;

This skips the first two rows and presents the next five in ascending order of customer IDs as shown in below.

cus_id	cus_name	amount	lane_id
003	Customer3	27000.00	1000
004	Customer4	25000.00	2000
005	Customer5	20000.00	1000
006	Customer6	18000.00	1000
007	Customer7	15000.00	1000

3. Combining Conditions

What if you need to fetch specific records from a lane and arrange them by amount in ascending order? The query might look like this:

SELECT * FROM customer_details WHERE lane_id = 1000 ORDER BY amount ASC LIMIT 3;

This combines a condition (lane_id = 1000) with sorting by amount in ascending order, yielding a concise and targeted results as shown in below.

cus_id	cus_name	amount	lane_id
007	Customer7	15000.00	1000
006	Customer6	18000.00	1000
005	Customer5	20000.00	1000