Beginners Guide to SQL – SQL TOP / MySQL LIMIT Clause

Hits: 18

(SQL Tutorials for Citizen Data Scientist)

SQL TOP / MySQL LIMIT Clause

In this tutorial you will learn how to retrieve fixed number of records from the table.

Limiting Result Sets

In some situations, you may not be interested in all of the rows returned by a query, for example, if you just want to retrieve the top 10 employees who recently joined the organization, get top 3 students by score, or something like that.

To handle such situations, you can use SQL’s TOP clause in your SELECT statement. However the TOP clause is only supported by the SQL Server and MS Access database systems.

MySQL provides an equivalent LIMIT clause, whereas Oracle provides ROWNUM clause for the SELECT statement to restrict the number of rows returned by a query.

SQL TOP Syntax

The SQL TOP clause is used to limit the number of rows returned. Its basic syntax is:

SELECT TOP number | percent column_list FROM table_name;

Here, column_list is a comma separated list of column or field names of a database table (e.g. nameagecountry, etc.) whose values you want to fetch. Let’s see how it works.

Suppose we’ve an employees table in our database with the following records:

+--------+--------------+------------+--------+---------+
| emp_id | emp_name     | hire_date  | salary | dept_id |
+--------+--------------+------------+--------+---------+
|      1 | Ethan Hunt   | 2001-05-01 |   5000 |       4 |
|      2 | Tony Montana | 2002-07-15 |   6500 |       1 |
|      3 | Sarah Connor | 2005-10-18 |   8000 |       5 |
|      4 | Rick Deckard | 2007-01-03 |   7200 |       3 |
|      5 | Martin Blank | 2008-06-24 |   5600 |    NULL |
+--------+--------------+------------+--------+---------+

The following statement returns top three highest-paid employees from the employees table.

-- Syntax for SQL Server Database  
SELECT TOP 3 * FROM employees
ORDER BY salary DESC;

The result set returned will look something like this:

+--------+--------------+------------+--------+---------+
| emp_id | emp_name     | hire_date  | salary | dept_id |
+--------+--------------+------------+--------+---------+
|      3 | Sarah Connor | 2005-10-18 |   8000 |       5 |
|      4 | Rick Deckard | 2007-01-03 |   7200 |       3 |
|      2 | Tony Montana | 2002-07-15 |   6500 |       1 |
+--------+--------------+------------+--------+---------+

You can optionally use the PERCENT keyword after the fixed value in a TOP clause, if you just want to retrieve the percentage of rows instead of fixed number of rows. Fractional values are rounded up to the next integer value (e.g. 1.5 rounded to 2).

The following statement returns top 30 percent of the highest-paid employees.

-- Syntax for SQL Server Database  
SELECT TOP 30 PERCENT * FROM employees
ORDER BY salary DESC;

The result set returned by the above query will look like this:

+--------+--------------+------------+--------+---------+
| emp_id | emp_name     | hire_date  | salary | dept_id |
+--------+--------------+------------+--------+---------+
|      3 | Sarah Connor | 2005-10-18 |   8000 |       5 |
|      4 | Rick Deckard | 2007-01-03 |   7200 |       3 |
+--------+--------------+------------+--------+---------+

MySQL LIMIT Syntax

The MySQL’s LIMIT clause does the same work as SQL TOP clause. Its basic syntax is:

SELECT column_list FROM table_name LIMIT number;

The following statement returns top three highest-paid employees from the employees table.

Example

-- Syntax for MySQL Database 
SELECT * FROM employees 
ORDER BY salary DESC LIMIT 3;

After execution, you’ll get the output something like this:

+--------+--------------+------------+--------+---------+
| emp_id | emp_name     | hire_date  | salary | dept_id |
+--------+--------------+------------+--------+---------+
|      3 | Sarah Connor | 2005-10-18 |   8000 |       5 |
|      4 | Rick Deckard | 2007-01-03 |   7200 |       3 |
|      2 | Tony Montana | 2002-07-15 |   6500 |       1 |
+--------+--------------+------------+--------+---------+

Note: In a SELECT statement, always use an ORDER BY clause with the LIMIT clause. Otherwise, you may not get the desired result.

Setting Row Offset in LIMIT Clause

The LIMIT clause accepts an optional second parameter.

When two parameters are specified, the first parameter specifies the offset of the first row to return i.e. the starting point, whereas the second parameter specifies the maximum number of rows to return. The offset of the initial row is 0 (not 1).

So, if you want to find out the third-highest paid employee, you can do the following:

Example

-- Syntax for MySQL Database 
SELECT * FROM employees 
ORDER BY salary DESC LIMIT 2, 1;

After executing the above command, you’ll get only one record in your result set:

+--------+--------------+------------+--------+---------+
| emp_id | emp_name     | hire_date  | salary | dept_id |
+--------+--------------+------------+--------+---------+
|      2 | Tony Montana | 2002-07-15 |   6500 |       1 |
+--------+--------------+------------+--------+---------+

 

Beginners Guide to SQL – SQL TOP / MySQL LIMIT Clause

 

Personal Career & Learning Guide for Data Analyst, Data Engineer and Data Scientist

Applied Machine Learning & Data Science Projects and Coding Recipes for Beginners

A list of FREE programming examples together with eTutorials & eBooks @ SETScholars

95% Discount on “Projects & Recipes, tutorials, ebooks”

Projects and Coding Recipes, eTutorials and eBooks: The best All-in-One resources for Data Analyst, Data Scientist, Machine Learning Engineer and Software Developer

Topics included: Classification, Clustering, Regression, Forecasting, Algorithms, Data Structures, Data Analytics & Data Science, Deep Learning, Machine Learning, Programming Languages and Software Tools & Packages.
(Discount is valid for limited time only)

Disclaimer: The information and code presented within this recipe/tutorial is only for educational and coaching purposes for beginners and developers. Anyone can practice and apply the recipe/tutorial presented here, but the reader is taking full responsibility for his/her actions. The author (content curator) of this recipe (code / program) has made every effort to ensure the accuracy of the information was correct at time of publication. The author (content curator) does not assume and hereby disclaims any liability to any party for any loss, damage, or disruption caused by errors or omissions, whether such errors or omissions result from accident, negligence, or any other cause. The information presented here could also be found in public knowledge domains.

Learn by Coding: v-Tutorials on Applied Machine Learning and Data Science for Beginners

Please do not waste your valuable time by watching videos, rather use end-to-end (Python and R) recipes from Professional Data Scientists to practice coding, and land the most demandable jobs in the fields of Predictive analytics & AI (Machine Learning and Data Science).

The objective is to guide the developers & analysts to “Learn how to Code” for Applied AI using end-to-end coding solutions, and unlock the world of opportunities!