TestingDocs.com
Software Testing website
  • Automation
    • Selenium
    • JBehave Framework
  • Tutorials
    • MySQL Tutorials
    • Testlink
    • Maven
    • Git
  • IDEs
    • IntelliJ IDEA
    • Eclipse
  • Flowcharts
    • Flowgorithm
    • Raptor
  • About

MySQL

MySQL SELECT LIMIT Statement

Overview

In this tutorial, we will learn MySQL SELECT LIMIT Statement with examples. This is an MySQL exclusive option to limit the query output. The LIMIT clause is used to restrict the number of rows retrieved by the query from the MySQL database.

Syntax

The syntax of the LIMIT clause is:

LIMIT row_count

LIMIT skip_count, row_count

This clause is useful when the table contains many rows and we want to view only few of the rows. Note that the LIMIT clause is optional.

For example,

SELECT [DISTINCT] columns_in_result
FROM table_name(s)
WHERE condition(s)
ORDER BY  column_name sort_order
LIMIT [skip_count,] row_count;

Example

We can add the LIMIT clause to limit the number of rows in the query.We will use the clause on the Country table from the world database.

mysql> SELECT Name, Capital FROM Country;

The above query will retrieve all the rows in the table. Let’s use the LIMIT clause to retrieve few rows. For example, to limit the number of rows in the query result to 10 rows.

mysql> SELECT Name, Capital FROM Country LIMIT 10;

 

MySQL SELECT Command

Using LIMIT skip count

The LIMIT clause can contain the skip_count to skip the results from the beginning and then display the rows specified by the row_count.

For example, the Country table has 239 rows.

mysql> SELECT COUNT(*) FROM Country;
+———-+
| COUNT(*) |
+———-+
| 239 |
+———-+
1 row in set (0.01 sec)

We can use the skip count to skip the rows of the result. For example, to display the last result, we can specify the skip count as 238 for the Country table.

mysql> SELECT Name, Population FROM Country LIMIT 238, 1;
+———-+————+
| Name | Population |
+———-+————+
| Zimbabwe | 11669000 |
+———-+————+
1 row in set (0.00 sec)

LIMIT Skip Count Clause

We can also use LIMIT clause with ORDER BY clause. MySQL server applies the ORDER BY clause first and then limits the result set with the LIMIT clause.

Let’s use the LIMIT clause to find the largest payment Amount from the Payment table in the sakila database. We will use the ORDER BY clause to sort the Amount column in descending order. The first row has the largest Amount value. We will limit the result to the first row with LIMIT 1.

 

mysql> SELECT Payment_id, Amount
-> FROM Payment
-> ORDER BY Amount DESC
-> LIMIT 1;

+————+——–+
| Payment_id | Amount |
+————+——–+
| 342 | 11.99 |
+————+——–+
1 row in set (0.01 sec)

 

LIMIT ORDER BY Clause Amount

—

MySQL Tutorials

MySQL Tutorials on this website:

https://www.testingdocs.com/mysql-tutorials-for-beginners/

For more information on MySQL Database:

https://www.mysql.com/

Related Posts

DataGrip Desktop Shortcut

MySQL /

Launch DataGrip on Windows

DataGrip Download

MySQL /

Install DataGrip IDE on Windows 11

MySQL Workbench Windows 11

MySQL /

New MySQL Connection in Workbench

MySQL Command-line Client

MySQL /

Command-Line MySQL Client

Start MySQL Client

MySQL /

Start MySQL Client on Windows 11

‹ MySQL SELECT ORDER BY clause› MySQL Replicate Table using Existing Table

Recent Posts

  • MS Access Data Types
  • Install RAPTOR Avalonia on CentOS
  • Download RAPTOR Avalonia Edition on Windows
  • npm doctor command
  • Build & Run CLion Project
  • Create New CLion C Project on Windows
  • Configure CLion Toolchains on Windows
  • Launch CLion IDE on Windows
  • Activate CLion IDE
  • CLion IDE for C/C++ Development

Back to Top

Links

  • Contact
  • Privacy Policy
  • Cookie Policy

www.TestingDocs.com

Go to mobile version