Limiting Results with LIMIT or TOP in SQL Server
The LIMIT keyword in SQL database management systems and the TOP keyword in SQL Server software are used to limit the number of table rows returned by a table query in an existing database table. Database users use the LIMIT or TOP keyword command when they want to receive and display only a subset of the table result values or numbers instead of all the rows of the database table.
Using the LIMIT keyword in MySQL, PostgreSQL, SQLite, etc.
The LIMIT command or clause in SQL and other database management systems, including database management software such as MySQL, PostgreSQL, and SQLite, is used to limit and preview the number of table rows displayed in the output of a database table result.
Syntax of the LIMIT keyword.
SELECT column_name(s) FROM table_name LIMIT number_of_rows;
Element of the LIMIT keyword.
- number_of_rows – This limits and previews the number of table rows returned in the existing database software table.
Example of the LIMIT keyword.
To display 2 employee records in the employee table.
SELECT * FROM employee LIMIT 2;
This displays and previews only the first 2 table row records from the existing employee table in the above employee table query.
Result of the LIMIT command for the first 2 employees.
employee_id emp_name emp_age department salary
101 Siddhi Deora 21 Marketing 74000
201 Harry Deora 23 Development 44000
Using the LIMIT command with OFFSET in MySQL, PostgreSQL, etc.
In MySQL and PostgreSQL database software, database users can also apply the OFFSET clause with the LIMIT command, where the database user determines from where to where (start point to end point) the existing database table record values should be returned and displayed. This feature is helpful in displaying the result output of a particular database page by page. For example, to preview database table records from row 7 to row 14.
Syntax of LIMIT with OFFSET.
SELECT column_name(s) FROM table_name LIMIT number_of_rows OFFSET offset_value;
Elements of LIMIT with OFFSET.
- number_of_rows – This is the number of table rows to be returned from the current database table.
- offset_value – This is the number of rows to skip from the beginning of the database table before displaying the output result values.
Example of LIMIT with OFFSET.
If a database user wants to display 6 employee records starting from the 3rd row in a database table, they can use the LIMIT command with the OFFSET statement.
SELECT * FROM employe LIMIT 3 OFFSET 3;
This database query returns 3 employee records starting from the 3rd table row.
employee_id emp_name emp_age department salary
301 Bhavshi Deora 37 Design 63000
401 Kunal Sharma 44 Engineering 98000
501 Vinay Sukla 33 Ui Designer 51000
Using the TOP statement in SQL Server.
In SQL Server database software, database users can use the TOP keyword instead of the LIMIT statement, allowing users to preview table record row number values from the start point to the end point in the database table.
Syntax of the TOP statement.
SELECT TOP number_of_rows column_name(s) FROM table_name;
Elements of the TOP statement.
- number_of_rows – This is the number of table rows to be returned from the database table.
Example of the TOP statement.
Here, the database user wants to display the top 3 employee records from the employee table.
SELECT TOP 3 * FROM employe;
This TOP statement query prints only the first 3 table record numbers from the existing employee table.
As a result, the top 3 employee records from the employee table are displayed.
employee_id emp_name emp_age department salary
101 Siddhi Deora 21 Marketing 74000
201 Harry Deora 23 Development 44000
301 Bhavshi Deora 37 Design 63000
Using LIMIT with the ORDER BY clause in database software.
Database users can return a specific number of table rows based on a particular sorting order of a database table. To manage this, the LIMIT statement can be used in conjunction with the ORDER BY clause.
Example of LIMIT with the ORDER BY clause.
To display the list of the top 3 employees with the highest salaries in the employee database table.
SELECT * FROM employee ORDER BY salary DESC LIMIT 3;
Here, this query sorts and displays the employee salaries in the employee table in descending order, from highest to lowest, and displays the top 3 employee table records with the highest salaries.
Result of LIMIT with the ORDER BY clause.
employee_id emp_name emp_age department salary
401 Kunal Sharma 44 Engineering 98000
101 Siddhi Deora 21 Marketing 74000
301 Bhavshi Deora 37 Design 63000
Conclusion of Limiting Results with LIMIT or TOP statement commands.
- LIMIT statement in MySQL, PostgreSQL, SQLite, etc. – The LIMIT statement limits the number of table records returned from a database table. Database users can also use the OFFSET statement to skip a specific number of table rows.
- TOP statement in SQL Server – This limits and displays the number of table records returned through a database table query in SQL Server.
- Using LIMIT and TOP with the ORDER BY clause – To display the most useful table rows in a particular order, such as the top N table value records, the LIMIT and TOP statements can be used in conjunction with the ORDER BY clause.
