Large websites often contain massive amounts of data, such as tens or even hundreds of thousands of customer records.
Imagine the challenge if a web system were to display all this data at once – it would be overwhelming.
I can’t keep up with the scrolling!
This is where the ‘LIMIT’ clause becomes handy, as it allows you to restrict the range of data being retrieved.
Take Google search, for example, which typically displays about 10 results per page from a vast number of websites.
In this article, we will explain how to use this ‘LIMIT’ clause.
▼youtube
Basics of LIMIT in SQL Syntax
The syntax for LIMIT is:
LIMIT start_position, number_of_rows
The LIMIT clause allows you to select and retrieve a specific number of rows, starting from a particular ‘start_position’ in the search results.
The Starting Position for LIMIT is ‘0’
When retrieving data using the LIMIT clause, the starting position begins from ‘0’.
For example, if you want to retrieve data starting from the third item, you would write:
LIMIT ２，〇
となります。
It’s similar to an ‘array’ in Java, isn’t it?
The ‘0’ in LIMIT Can Be Omitted
To retrieve the first five rows of data, you would normally write:
LIMIT ０，5;
However, the initial ‘0,’ in this statement can be omitted.
Therefore, you can simply use:
LIMIT 5;
This is sufficient to retrieve the first five rows.
Displaying the Information of the Student with the Highest Score
To show the student with the highest score, you can use the following SQL query:
SELECT * FROM students
ORDER BY entry_exam DESC
LIMIT 1;
By using ORDER BY along with DESC, the data is sorted in descending order.
LIMIT 1;
Then, by applying LIMIT 1, it retrieves only the top record, which displays the student with the highest score.
Displaying the Name of the Student with the Highest Score
You can use a subquery for this purpose
SELECT * FROM students
WHERE entry_exam = (
SELECT MAX(entry_exam)
FROM students
);
In this way, you can insert a SELECT statement within the WHERE clause.
A SQL statement within another SQL statement is referred to as a subquery.
Summary: Limiting the Range of Data with MySQL LIMIT
The LIMIT clause is quite a commonly used SQL statement.
By utilizing it, you could create your own search system, or even a system to record and retrieve only the top-rated restaurants based on their scores.
