Back to list MySQL How to Limit Rows in a MySQL Result Set Database: MySQL Operators:LIMIT Problem: You’d like to limit the number of rows in a result set in MySQL. Example: Our database has a table named student with data in the columns id, first_name, last_name, and age. idfirst_namelast_nameage 1StevenWatson25 2LisaAnderson19 3AliceMiller19 4MaryBrown25 5LucyWatson25 6MichaelJackson22 Let’s select students’ full names and ages, but limit the rows returned to three. Solution: SELECT first_name, last_name, age FROM student LIMIT 3; Here’s the result of the query: idfirst_namelast_nameage 1StevenWatson25 2LisaAnderson19 3AliceMiller19 Discussion: The LIMIT clause limits the number of rows in the result set. It is used in the SELECT statement, usually at the end of the statement. (Even ORDER BY should be listed before LIMIT.) The only required argument is the number of rows to display. In our example, LIMIT 3 produced a result set containing three rows. Unless you specify otherwise with the OFFSET argument, this function will always return the first n rows that meet query requirements. LIMIT’s optional OFFSET argument is placed before the argument indicating the number of returned rows. It indicates the position of the first row returned by LIMIT (i.e. ‘0’ is the first row, 1 is the second row, etc.). The offset and the number of rows are separated by a comma. The query returns the same records as the previous query because the offset is zero. (Zero is the default offset value.) SELECT first_name, last_name, age FROM student LIMIT 0, 3; In these queries, the result rows are unordered. If you’d like to select three rows from a sorted result set, use ORDER BY: SELECT first_name, last_name, age FROM student ORDER BY age DESC, last_name, first_name LIMIT 1,3; Here, we’re first ordering the rows by age (descending), then last name, then first name. We use the offset value to start on the second returned row and limit the results to three rows: first_namelast_nameage LucyWatson25 StevenWatson25 MichaelJackson22 This query first sorts rows according to the age column in descending order. It then sorts by last_name and first_name in ascending order. If you look at the table, you’ll see that the oldest students (Steven, Lucy, Mary) are 25 years old. However, Mary is omitted because she is the first row and the offset is 1 (i.e. we’re starting with the second row). Lucy is now first because her first name comes before Steven’s. (Steven and Lucy have the same last name, so their order is determined by first name.) Michael is 22, which makes him the next-oldest student and last of the three rows returned. Recommended courses: SQL Basics SQL Basics in SQL Server Standard SQL Functions SQL Practice Set Recommended articles: 5 SQL Functions for Manipulating Strings 18 Useful Important SQL Functions to Learn ASAP Performing Calculations on Date- and Time-Related Values How Often Employees Are Running Late for Work: SQL Datetime and Interval SQL Arithmetic See also: How to Replace Part of a String in SQL How to Find the Number of Days Between Two Dates in MySQL How to Find the Last Day of the Month for a Given Date in MySQL How to Change Seconds to a Time Value in MySQL How to Change Datetime Formats in MySQL How to Get the Date from a Datetime Column in MySQL How to Get the Current Date and Time in MySQL How to Add Days to a Date in MySQL How to Add Time to a Datetime Value in MySQL Subscribe to our newsletter Join our weekly newsletter to be notified about the latest posts.