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

    Stellar Converter for Database Tool

    MySQL /

    Stellar Converter for Database

    Stellar MySQL Log Analyzer

    MySQL /

    Stellar Log Analyzer for MySQL

    Stellar Repair for MySQL

    MySQL /

    Stellar Repair for MySQL

    MySQL /

    How to secure your SQL Database: Tips and Tricks

    Stellar Converter for Database

    MySQL /

    Database Converter Tools

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

    Recent Posts

    • ChatGPT Plans Free and PlusChatGPT Subscription Plans
    • Stellar Converter for Database ToolStellar Converter for Database
    • Stellar MySQL Log AnalyzerStellar Log Analyzer for MySQL
    • Stellar Repair for MySQLStellar Repair for MySQL
    • ChatGPT IntroductionChatGPT Capabilities
    • How to secure your SQL Database: Tips and Tricks
    • ChatGPT4 Conversational AI FeaturesChatGPT4 Conversational AI Features
    • Trends in Software EngineeringShaping the Future of Development: Exploring Key Trends in Software Engineering
    • Java PerformanceImproving Java Performance with Multithreading
    • QDrant Vector DatabaseOpen-source Vector Databases
    • Difference between PHP and JavaScript?
    • Bing AI Browser Web ContentBing Conversation Styles
    • ChatGPT PreviewChatGPT Introduction
    • Open Source AI Frameworks TensorFlowOpen Source AI Frameworks
    • Artificial Intelligence Tools

    Back to Top

    Links

    • Contact
    • Privacy Policy
    • Cookie Policy

    www.TestingDocs.com