Meritshot Tutorials
SQL Tutorial
-
SQL SyntaxSQL Syntax
-
SQL ORDER BY ClauseSQL ORDER BY Clause
-
Introduction to SQLIntroduction to SQL
-
SQL SELECT DISTINCTSQL SELECT DISTINCT
-
SQL Logical OperatorsSQL Logical Operators
-
SQL WHERE ClauseSQL WHERE Clause
-
SQL SELECT StatementSQL SELECT Statement
-
SQL DELETE StatementSQL DELETE Statement
-
SQL INSERT INTOSQL INSERT INTO
-
SQL Null ValuesSQL Null Values
-
SQL Update StatementSQL Update Statement
-
SQL Select TopSQL Select Top
-
SQL Aggregate FunctionsSQL Aggregate Functions
-
SQL LIKE and WildcardsSQL LIKE and Wildcards
-
SQL IN and SQL BETWEENSQL IN and SQL BETWEEN
-
SQL JOINSSQL JOINS
-
SQL Group BySQL Group By
-
SQL HavingSQL Having
-
SQL EXISTSSQL EXISTS
-
SQL SELECT INTOSQL SELECT INTO
-
SQL INSERT INTO SELECTSQL INSERT INTO SELECT
-
SQL CASE STATEMENTSQL CASE STATEMENT
-
SQL NULL FunctionsSQL NULL Functions
-
SQL Stored ProceduresSQL Stored Procedures
-
SQL User-Defined FunctionsSQL User-Defined Functions
-
SQL CommentsSQL Comments
-
SQL OperatorsSQL Operators
-
SQL Database Creation and ManagementSQL Database Creation and Management
-
SQL CREATE DATABASE StatementSQL CREATE DATABASE Statement
-
SQL CREATE TABLE StatementSQL CREATE TABLE Statement
-
SQL DROP DATABASE StatementSQL DROP DATABASE Statement
-
SQL DROP TABLE StatementSQL DROP TABLE Statement
-
SQL ALTER TABLE StatementSQL ALTER TABLE Statement
-
SQL NOT NULL ConstraintSQL NOT NULL Constraint
-
SQL UNIQUE ConstraintSQL UNIQUE Constraint
-
SQL PRIMARY KEY ConstraintSQL PRIMARY KEY Constraint
-
SQL FOREIGN KEY ConstraintSQL FOREIGN KEY Constraint
-
SQL CHECK ConstraintSQL CHECK Constraint
-
SQL DEFAULT ConstraintSQL DEFAULT Constraint
-
SQL IndexesSQL Indexes
-
SQL Date FunctionsSQL Date Functions
-
SQL ViewsSQL Views
-
SQL InjectionSQL Injection
-
SQL Data Types OverviewSQL Data Types Overview
-
SQL AUTO_INCREMENTSQL AUTO_INCREMENT
-
SQL Keywords ReferenceSQL Keywords Reference
SQL SELECT TOP
The SELECT TOP clause is used to limit the number of rows returned by a query. It’s particularly useful when you want to retrieve only a subset of the data, such as the first few records or the top N records based on specific criteria.
Basic Syntax
Syntax:
SELECT TOP (number) column1, column2, …
FROM table_name
ORDER BY column;
- number: Specifies the number of rows to return.
- column: The column by which the results are ordered.
Note: The ORDER BY clause is often used in conjunction with SELECT TOP to ensure that the rows returned are the top N rows based on a specific sorting criterion.
Example 1: Retrieve Top N Records
Get the top 3 highest-paid employees from the employees table.
Example:
Get the top 3 highest-paid employees from the employees table.
SELECT TOP (3) employee_id, first_name, last_name, salary
FROM employees
ORDER BY salary DESC;
Output:
employee_id | first_name | last_name | salary |
3 | Priya | Verma | 70000 |
11 | Deepak | Jain | 66000 |
1 | Rajesh | Singh | 65000 |
Example 2: Retrieve Top Percentage of Records
Get the top 10% of the employees based on their salary.
Example:
SELECT TOP (10) PERCENT employee_id, first_name, last_name, salary
FROM employees
ORDER BY salary DESC;
Output:
If the table has 20 employees, this query will return the top 2 employees (10% of 20) with the highest salaries.
employee_id | first_name | last_name | salary |
3 | Priya | Verma | 70000 |
11 | Deepak | Jain | 66000 |
Example 3: Retrieve Top N Records with a Specific Condition
Get the top 5 youngest employees from the employees table who are in the ‘IT’ department.
Example:
Get the top 5 youngest employees from the employees table who are in the ‘IT’ department.
SELECT TOP (5) employee_id, first_name, last_name, age
FROM employees
WHERE department = ‘IT’
ORDER BY age ASC;
Output:
employee_id | first_name | last_name | age |
8 | Manish | Patel | 25 |
9 | Ananya | Kumar | 28 |
11 | Deepak | Jain | 30 |
2 | Aarti | Mehta | 31 |
12 | Meera | Patel | 32 |
Tips to Remember
- Specify ORDER BY: Use the ORDER BY clause with SELECT TOP to ensure the rows returned are based on the desired sorting criterion.
- Percentage Limitation: When using a percentage, ensure that it is appropriate for the size of your dataset. For example, 10% of 5 rows will return 0.5 rows, which will be rounded to 1 row.
- Combining with Other Clauses: You can combine SELECT TOP with other clauses like WHERE and JOIN to refine your results.
- Database Compatibility: Different SQL databases have variations in syntax. For example, in MySQL, you would use LIMIT instead of TOP.
Power of SELECT TOP
The SELECT TOP clause:
- Limits Results: Allows you to restrict the number of rows returned, making it easier to work with large datasets.
- Focuses on Specific Data: Helps in retrieving the most relevant data, such as top performers or recent entries.
- Optimizes Performance: Improves query performance by reducing the amount of data processed and returned.
Frequently Asked Questions
Q1: Can I use SELECT TOP without ORDER BY?
A1: It is possible, but the results might be arbitrary. Using ORDER BY ensures that the rows returned are the top N rows based on a specific criterion.
Q2: How does SELECT TOP differ from LIMIT in MySQL?
A2: LIMIT is used in MySQL to achieve similar functionality as SELECT TOP in SQL Server. The syntax is different, but the purpose is the same.
Example in MySQL:
SELECT employee_id, first_name, last_name, salary
FROM employees
ORDER BY salary DESC
LIMIT 3;
Q3: Can I combine SELECT TOP with GROUP BY?
A3: Yes, you can combine SELECT TOP with GROUP BY and ORDER BY to retrieve the top records from each group.
Example:
SELECT TOP (3) WITH TIES department, AVG(salary) AS avg_salary
FROM employees
GROUP BY department
ORDER BY avg_salary DESC;
Q4: How does SELECT TOP work with DISTINCT?
A4: SELECT TOP can be used with DISTINCT to return a specific number of unique rows.
Example:
SELECT TOP (5) DISTINCT department
FROM employees;
Q5: Can I use SELECT TOP in subqueries?
A5: Yes, you can use SELECT TOP in subqueries to limit the number of rows processed in the outer query.
Example:
SELECT employee_id, first_name, last_name
FROM (SELECT TOP (5) employee_id, first_name, last_name
FROM employees
ORDER BY salary DESC) AS TopSalaries;
