🍿 CornPop

TOP

5 min read

SELECT TOP

The SELECT TOP clause is used in SQL to limit the number of rows returned by a query. This is particularly useful when you want to retrieve only a subset of rows, such as the first few records from a table (e.g., the top 5 highest-paid employees).

Purpose:

  • The TOP clause limits the number of rows returned by a query.
  • It is often used when sorting data in a specific order (e.g., highest salary, latest date) to get the top N results.

Syntax:

SELECT TOP N column1, column2
FROM table_name
ORDER BY column_name DESC;
  • N: The number of rows to return.
  • ORDER BY: You should typically use ORDER BY to define the criteria for selecting the top rows (e.g., by salary or date).

SELECT TOP with Percentage

When you use TOP with a percentage, SQL will return the specified percentage of the rows from the result set, based on the total number of rows after applying the ORDER BY clause.

Purpose:

  • Retrieve a specific percentage of rows from the query results.
  • Often used when you want to get a portion of the data (e.g., the top 10% of employees based on salary).

Syntax:

SELECT TOP (percentage) PERCENT column1, column2
FROM table_name
ORDER BY column_name;
  • PERCENT: Specifies that the rows should be returned as a percentage of the total rows.
  • ORDER BY: Always used with ORDER BY to define how the rows are sorted before calculating the percentage.

Using TOP with ORDER BY vs. Without ORDER BY

The TOP clause in SQL is most effective when used in combination with the ORDER BY clause. It helps you retrieve the first N rows or a percentage of rows based on a specific ordering of the data.

However, TOP can technically be used without ORDER BY, but the results may not always be predictable or meaningful. Here’s an explanation of when and how to use it with or without ORDER BY.

1. TOP with ORDER BY

Purpose:
  • You use ORDER BY with TOP to define the criteria for which rows should be returned (e.g., the highest salaries, latest dates, etc.).
  • ORDER BY makes sure that the rows are sorted in a specific order, and then TOP picks the first N rows from that sorted data.
When to Use:
  • When you need to retrieve specific rows based on a certain order. For example, the top 5 highest-paid employees, the most recent 10 orders, etc.
Syntax:
SELECT TOP N column1, column2
FROM table_name
ORDER BY column_name DESC;  -- or ASC for ascending order
Example:
SELECT TOP 3 employee_name, salary
FROM Employees
ORDER BY salary DESC;

Output:

employee_namesalary
John70000
Mike65000
Alice60000

Explanation: The query returns the top 3 employees ordered by their salary in descending order.

  • ORDER BY salary DESC ensures that the highest salaries are selected first.
  • The TOP 3 picks the first 3 rows after sorting.

2. TOP without ORDER BY

Purpose:
  • You can use TOP without ORDER BY when you just want to return the first N rows from the table as they appear in the database, which is typically arbitrary.
  • No predictable sorting: Since there’s no sorting, SQL Server will return the first rows it encounters based on how data is physically stored or retrieved from the table, which is often not ordered.
When to Use:
  • Use TOP without ORDER BY if you don’t need any specific order and just want the first N rows of the table (this is rare and often not recommended in most real-world scenarios).
Syntax:
SELECT TOP N column1, column2
FROM table_name;
Example:
SELECT TOP 3 employee_name, salary
FROM Employees;

Output:

employee_namesalary
John70000
Mike65000
Alice60000

Explanation: The query returns the first 3 rows of the table based on how the database retrieves the rows. Without an ORDER BY clause, the rows are not guaranteed to be sorted by any specific column, so the output may change each time depending on how SQL Server decides to fetch the rows.

Key Differences and Recommendations

With ORDER BYWithout ORDER BY
Guarantees a specific order based on the column(s) you define.Returns arbitrary rows (unpredictable).
Useful for selecting the top N rows based on specific criteria.Rarely used, typically doesn’t make sense without order.
Example: Get top 3 highest salaries.Example: Get the first 3 rows (order is not defined).

Summary:

  • Always use TOP with ORDER BY when you want to retrieve rows in a specific order (e.g., top N highest salaries, most recent orders).
  • Avoid using TOP without ORDER BY if you need predictable results. Without ORDER BY, the result is arbitrary, and the rows returned might differ each time the query runs, making it unreliable for most use cases.

Example Table (Employee Data):

employee_idemployee_namesalary
1John70000
2Mike65000
3Alice60000
4Sarah55000
5David50000

Example Query (Normal):

SELECT TOP 3 employee_name, salary
FROM Employees
ORDER BY salary DESC;

Output:

employee_namesalary
John70000
Mike65000
Alice60000

Explanation:

  • The SELECT TOP 3 query retrieves the top 3 highest-paid employees from the Employees table, based on the salary column.
  • The query uses ORDER BY salary DESC to sort the results in descending order, ensuring that the employees with the highest salaries are returned first.

Example Query (Get Top 40% of Employees by Salary):

SELECT TOP 40 PERCENT employee_name, salary
FROM Employees
ORDER BY salary DESC;
Explanation:
  • The query will return the top 40% of employees based on their salary in descending order.
  • In this case, 40% of 5 rows would be 2 rows, so the query will return the top 2 highest-paid employees.
Output Example:
employee_namesalary
John70000
Mike65000

Summary:

  • SELECT TOP N: Retrieves a fixed number of rows.
  • SELECT TOP N PERCENT: Retrieves a specific percentage of rows based on the total result set.

Graph View