SQL Limit Order By

Introduction to SQL Limit Order By

The limit clause in Mysql is used to restrict the number of the rows retrieved in the resultset of the query to a certain count. It also helps us to retrieve the particular number of the records beginning from a particular offset from the MySQL table. Whenever the tables contain a huge amount of records, it becomes a heavy operation and even time-consuming to retrieve all these records and display them. This affects the performance of the application. The order by clause helps us define which records are to be retrieved in the limited result set that we have specified using the limit clause. In this topic, we are going to learn about SQL Limit Order By.

Syntax

The limit clause accepts two arguments. Out of which one is required, that is count_of_rows is required, and the other one named offset is optional. ORDER BY clause can consist of ordering the data based on one or more column values ascending or descending. The syntax of the LIMIT clause, along with the ORDER BY clause and the place where they should be used, is shown below:

SELECT
selected_columns_or_expressions
FROM
name_of_table
ORDER BY columns ASC/DESC
LIMIT [offset,] count_of_rows;

The count_of_rows is the integer value that helps us to specify the row count up to which the number of the records is to retrieve from the name_of_table table. The offset is the integer value that is optional and has the default value is 0. The offset is used for specifying the position of the record from where the result set is to be fetched from the table named name_of_table. The following figure will help you to understand the offset and row_count concepts clearly –

Consider that the above diagram shows the records of the table, and the numbers in it stand for the row number of that records. If the offset is specified as 3, then the row count will begin from the fourth row as the offset begins from 0 by default. When the row_count is mentioned as 4, then starting from the defined offset, i.e. in this case, from the fourth row, four records will be retrieved as the row_count is 4. If the row_count will have been 5, then beginning from the fourth record, 5 rows would have been retrieved with row numbers 4,5,6,7 and 8.

Whenever we do not specify the offset value, the default value is considered 0, and the row count begins from the 1-row number. In that case, both of the following limit clauses work in the same way:

LIMIT 5;

and

LIMIT 0,5;

Both of them will retrieve the first 5 records from the select query’s resultset.

In the ORDER BY clause, we can specify a list of columns on which we want to define the order of the resultset and then mention whether the order should be in an ascending or descending manner using ASC and DESC. By default, when not specified the type of order, it is considered ascending type.

Alternative Syntax for LIMIT

The LIMIT clause has one more optional syntax provided to maintain compatibility with the PostgreSQL syntax that is as follows.

LIMIT count_of_rows OFFSET offset;

Using LIMIT clause along with ORDER BY clause

Whenever we retrieve the records without using the ORDER BY clause, then the result is retrieved in any order, and when the LIMIT clause is applied to such queries, then the random rows with a limited number of row count will be required which is generally not what we want. The order in which the rows are retrieved plays an essential role in specifying what records are to be retrieved. It is a good practice to use the LIMIT clause along with the ORDER BY clause.

Thus, we can specify the ORDER BY clause with the field on which the ordering is to be done while retrieving the records and restricting the rows to be retrieved from the ordered data using the LIMIT clause. The order in which the execution of the clauses in the SELECT statement is considered is displayed in the below figure –

We can observe that all the clauses are executed before the LIMIT clause, and finally, while retrieving the resultset, the last thing that is executed is to consider the row count and offset from the resultset that needs to be retrieved.

Example

Let us create one table named educba_writers using the following query statement

CREATE TABLE `educba_writers` (
`id` int(11) NOT NULL,
`firstName` varchar(10) COLLATE latin1_danish_ci NOT NULL,
`rate` decimal(5,2) DEFAULT NULL,
`joining_date` date DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_danish_ci;

Let us insert some more rows with a non-null rate and joining date value

INSERT INTO `educba_writers` (`id`, `firstName`, `rate`, `joining_date`) VALUES
(1, 'Payal', 750, "2020-05-01"),
(2, 'Vyankatesh', 700, "2020-01-01"),
(3, 'Omprakash', 600, "2020-02-01"),
(4, 'sakshi', 200, "2020-06-01"),
(5, 'prerna', 150, "2020-02-01"),
(6, 'preeti', 350, "2020-06-01"),
(7, 'sanjana', 400, "2020-02-01"),
(8, 'omkar', 450, "2020-06-01"),
(9, 'sohail', 650, "2020-02-01"),
(10, 'soniya', 850, "2020-06-01"),
(11, 'supriya', 700, "2020-02-01"),
(12, 'saniya', 750, "2020-06-01"),
(13, 'omkar', 410, "2020-02-01"),
(14, 'akshay', 910, "2020-06-01"),
(15, 'akash', 730, "2020-02-01"),
(16, 'siddharth', 980, "2020-06-01");
select * from educba_writers;

that gives the following output

Now, suppose that we want to retrieve the list of the 5 highest rated writers from the educba_writers table. For this, it is necessary to first order the resultset based on the rate column in the descending order so that the highest paid of them will be at the first one to be retrieved. Further, as we have to retrieve only the top 5 writers, we will use the limit clause for specifying the same. Our query will be as follows:

select firstName, rate
FROM
educba_writers
ORDER BY rate DESC
LIMIT 5;

that gives the following output after execution –

Conclusion

We can restrict the number of records to be retrieved from the result set by using the LIMIT clause in MySQL. Further, which records are to be retrieved can be specified using the ORDER BY clause as it makes sense to retrieve the resultset with first or last criteria values which is generally the use case.

Recommended Articles

This is a guide to SQL Limit Order By. Here we discuss the Using LIMIT clause and the ORDER BY clause, and the Example and Outputs. You may also have a look at the following articles to learn more –

1. SQL For loop
2. SQL Mapping
3. SQL Clone Table
4. PostgreSQL OR