SQL Server: SELECT TOP Statement

We can use TOP Clause in a SQL Update statement as well to restrict the number of rows for an update. Essentially, it is a combination of the select statement and update. In the following query, we use the TOP clause in the select statement and update the values in the [HumanResources.Employee] table.

This SQL Server SELECT TOP example would select the first 10% of the records from the full result set. So in this example, the SELECT statement would return the top 10% of records from the employees table where the last_name is ‘Anderson’. The other 90% of the result set would not be returned by the SELECT statement. The following query inserts basic database queries the top 10 records from a select statement into [TempInsert] table. First, let’s create a SQL table using the CREATE TABLE statement with relevant data types. The SQL SELECT TOP statement is used to retrieve records from one or more tables in a database and limit the number of records returned based on a fixed value or percentage.

Оператор SQL TOP

In this syntax, the SELECT statement can have other clauses such as WHERE, JOIN, HAVING, and GROUP BY. For example, here I will show you how to take certain records from one table and insert those records into the new table. As you can see in the result set, there are only 5 Products with ProductName, SalesDate, and SalesPrice. For example, Laptop Computer, Smartphone, LET TV, Digital Camera, and Wireless Mouse. Also, you will learn two ways to use the TOP clause based on the number and percentage value.

In the following query, we retrieve records from the SQL table order by [SickLeaveHours] column. While these examples might seem straightforward, common mistakes can trip you up. For instance, omitting the ORDER BY clause is a frequent error that leads to unpredictable results. Another common oversight is not specifying enough conditions within the ORDER BY clause, which might not narrow down the results as intended. The ORDER BY statement is crucial here; without it, we wouldn’t be assured of getting the highest earners, just a random set of 5 employees.

Data Types

In SQL Server, this is the standard syntax, but it’s essential to be aware of variations across different database systems. For instance, in MySQL and PostgreSQL, the LIMIT clause and the LIMIT and FETCH clauses are used respectively, instead of TOP. By adding PERCENT after the TOP 10 in the SELECT statement, instead of getting
10 rows, we get 10 percent of the rows returned.

Then, you learned how to limit the number of rows based on the numeric and percentage values. You have to create a table named ‘TopSalesPrice’ containing the top 10 product_ids, the product name, and the sales price. This information will be taken from the Sales table in the above sections. We can use a variable to define the number of records in the TOP statement. In the following query, we defined an int variable @i and specified value 10 for it.

SQL TOP PERCENT Example

Note that WITH TIES may result in more rows being returned than specified in the expression. In this SQL Server tutorial, you learned how to limit the number of rows in a query result set using the SQL Server TOP clause. Here, you can specify any percentage like TOP 40 PERCENT, TOP 80 PERCENT, etc.

However, the concept of limiting the number of rows returned in a query remains consistent across various database systems. Then SELECT TOP 2 takes the first two records from the order rows data and includes them in the result set. This is how to apply the SQL Server TOP clause to limit the number of records in the query result set. For example, you must find the top 3 products with the highest sales price. Here, you can use the ORDER BY clause as specified in the above syntax to order the rows of data in your result set, and the TOP clause will limit the rows to 2. Here, `(number|percent)` specifies the exact number of records or the percentage of records you wish to pull from the database.

SQL TOP

Instead of 5, you can get 10, 20 or even more of this number of rows in your result set. But as you can see, the order of rows is random; I mean, you can order the rows of the data in ascending (from low to high) and descending (from high to low). It is like the LIMIT clause in the other Relational Databases such as MySQL and PostgreSQL. We use the OUTPUT clause to display the records inserted in the TempInsert table. We can use TOP Clause in insert statement as well, but it is recommended to use it in the Select statement. If we use it in the INSERT clause, it might cause performance issues, especially for a large table.

The TOP clause can be used in a subquery to determine records to delete. All the records in the VehicleTemperatures table are returned, totaling
65,998. The SELECT TOP clause is useful on large tables with thousands of
records. The following statement returns top 30 percent of the highest-paid employees.

In this article, we have talked about the SQL SELECT TOP statements, this query structure helps to limit the result
set of the queries. At the same time, we have learned some alternatives to the TOP clauses. At the same time, we can use the PERCENT keyword on the update statements. This numerical expression defines how many rows are returned from the query. For instance, when we want to return
the first 10 rows of the table we can set this parameter as 10. In this example, we retrieve random 5 rows from the
Product table.

• Adding ORDER BY SUM([AmountExcludingTax]); orders the records by
  the sum of the pretax amount in ascending order.
• The following query inserts the top 10 records from a select statement into [TempInsert] table.
• As a result, you would get the names of the top 5 highest-paid employees in the organization.
• Following the TOP keyword is an expression that specifies the number of rows to be returned.

Add the ORDER BY keyword when you want to sort the result, and return the first 3 records of the sorted result. From the output, the TopSalesPrice contains the top 10 products from the Sales table. Use the query below to insert the top 10 products with product prices in TopSalesPrice. We should use the ORDER BY clause in the delete statement to avoid unnecessary deletion of data. In the next query, we specify an ORDER BY Clause to sort results in descending order of birthdate column. Let’s look at a few examples of the SQL Server TOP clause for [HumanResources.Employee] table in the AdventureWorks sample database.

Fetching Top N Records

The SQL TOP clause used to limit the number of rows returned by a query. It is often used when you want to retrieve a specific number of rows from a table that meet certain criteria, or when you want to retrieve the first N rows of a result set. SQL TOP is supported by various database management systems, although the syntax may differ slightly between them. The WITH TIES keyword enables to include the rows into the result set that matches with the last
row.