The SQL SELECT TOP Clause
The SQL SELECT TOP clause is used to limit the number of rows returned by a query. It allows you to retrieve a specified number of rows from the result set.
Let me explain it with examples.
The SQL SELECT TOP clause is used for large numbers of records in a database table. MYSQL supports the LIMIT clause, while Oracle uses ROWNUM, FETCH FIRST N ROWS ONLY.
SQL SELECT TOP for SQL Server or MS Access databases:
SQL> SELECT TOP number|percent column_name FROM table_name
WHERE condition;
Syntax for MySQL database:
SQL> SELECT column_name FROM table_name
WHERE condition
LIMIT number;
Syntax for Oracle databases:
SQL> SELECT column_name FROM table_name
ORDER BY column_name
FETCH FIRST number ROWS ONLY;
Syntax for Oracle database with ORDER BY Clause:
SQL> SELECT * FROM (SELECT column_name FROM table_name ORDER BY column_name)
WHERE ROWNUM <= number;
Example 1: Retrieve the Top N Rows
Suppose you have a table called “Employees” with columns for employee names and salaries. You want to retrieve the top 5 employees with the highest salaries. You can use the SELECT TOP clause like this:
SQL> SELECT TOP 5 EmployeeName, Salary FROM Employees
ORDER BY Salary DESC;
In this example,
SELECT TOP 5: This specifies that you want to retrieve the top 5 rows.
EmployeeName, Salary: This specifies the columns you want to retrieve data from.
FROM Employees: Specifies the table you are querying.
ORDER BY Salary DESC: Order the rows by the “Salary” column in descending order, so you get the highest salaries first.
More examples of SQL SELECT TOP Clause
Example 2: Retrieve the Top N Percent of Rows
You can also use the SELECT TOP clause to retrieve a specific percentage of rows. For example, if you want to retrieve the top 10% of the highest-paid employees, you can do this:
SQL> SELECT TOP 10 PERCENT EmployeeName, Salary FROM Employees
ORDER BY Salary DESC;
In this case, you specify TOP 10 PERCENT to get the top 10% of rows based on the “Salary” column.
Example 3: Retrieve the First Row
If you want to retrieve just the first row from a table, you can use SELECT TOP 1 like this:
SQL> SELECT TOP 1 EmployeeName, Salary FROM Employees
ORDER BY Salary DESC;
This will give you the employee with the highest salary.
Example 4: Retrieve Tied Rows
When multiple rows have the same value in the column used for ordering and you want to retrieve all tied rows, you can use the WITH TIES option. For instance, to retrieve the top 5 employees with the highest salaries and include any tied rows:
SQL> SELECT TOP 5 WITH TIES EmployeeName, Salary FROM Employees
ORDER BY Salary DESC;
This will return the top 5 employees with the highest salaries and include any additional employees who have the same salary.
These examples show how the SELECT TOP clause can be used to specify a maximum and minimum number of rows to return from SQL queries.