IT Education
SQL SELECT TOP statement overview and examples
Let’s look at a SQL example, where we use the TOP keyword in the SELECT statement. Adding DESC to the end of ORDER BY SUM([AmountExcludingTax]);
changes the sort order to descending. Adding ORDER BY SUM([AmountExcludingTax]); orders the records by
the sum of the pretax amount in ascending order. Below are the supplier IDs and the sums of pretax amounts for each returned in
no particular order.
Two percent of 290 is 5.8 that is a fractional value, so SQL Server rounds output to six rows (next whole number). In this example, we retrieve the top 10 records from a SQL table without specifying any condition and ORDER BY Clause. This article explores the SQL Server TOP clause using various examples, along the way, we will also run through performance optimization while using the TOP clause in SQL Server. This SQL tutorial explains how to use the SQL SELECT TOP statement with syntax and examples.
Example 2: TOP Clause with a PERCENT value
For this section, let’s generate some test data and use the TOP clause in the select statement. This SQL SELECT TOP example would select the first 5 records from the contacts table where the last_name is ’Anderson’. If there are other records in the contacts table that have a last_name of ’Anderson’, they will not be returned by the SELECT statement. These practical examples highlight the versatility and potential pitfalls of the SQL TOP clause.
- This adjustment makes the query not just powerful, but also predictable and useful for reporting or analytical purposes.
- It’s a fantastic way to retrieve a slice of data based on proportion, rather than a fixed number.
- This is how to apply the SQL Server TOP clause to limit the number of records in the query result set.
- In MySQL or PostgreSQL, this is how we’d achieve the same result as our first example.
We need to take into account one point about the WITH TIES, usage of this expression in the queries may cause
more rows to be returned than we specify in the TOP expression. For example, if we want to retrieve the highest cost
product we can use the TOP 1 keyword. However, if we add the WITH TIES keyword to the SQL SELECT TOP statement, the
query will return all rows which have the same cost.
How to Use TOP in SQL with Examples
To return only the highest 50 percent of pretax amounts is achieved by adding
a TOP 50 PERCENT to the SELECT statement. The following examples were run in SQL Server Management Studio (SSMS) 19.2 against
a copy basic database queries of the
Wide World Importers sample database on a Microsoft SQL Server 2022 server. In MySQL, the LIMIT clause provides similar functionality, and in Oracle SQL, the ROWNUM clause serves the same purpose.
Understanding the nuances and applying these practices in real-world scenarios elevates the precision and performance of data retrieval in SQL. Practicing with these variations and avoiding common pitfalls is essential in mastering the SQL TOP clause. This query lacks clarity on what “top” means, rendering it essentially random.
Contact Sales
Always pair the TOP clause with ORDER BY to ensure the results are meaningful and predictable. In MySQL or PostgreSQL, this is how we’d achieve the same result as our first example. Each database has its nuances, so it’s important to adapt the syntax accordingly.
In this example, the query retrieves the first 5 rows from the “Employees” table, ordered by the “Salary” column in descending order. As a result, you would get the names of the top 5 highest-paid employees in the organization. When we use a TOP clause with an update statement, the update runs for the undefined rows because we could not add
ORDER BY to this type of update statement. Here, column_list is a comma separated list of column or field names of a database table (e.g. name, age, country, etc.) whose values you want to fetch.
SQL Server SELECT TOP
The following statement returns top three highest-paid employees from the employees table. In this tutorial you will learn how to retrieve fixed number of records from the table. It will include not only the first expensive product but also the second one, and so forth.
This adjustment makes the query not just powerful, but also predictable and useful for reporting or analytical purposes. Navigating through vast databases can feel like searching for a needle in a haystack. It’s my go-to tool for slicing through data clutter, allowing me to retrieve just the cream of the crop. Whether you’re a budding data analyst or a seasoned database manager, mastering SQL TOP is a game-changer.
Remember, though, that the ‘top’ records will depend on how your data is ordered. It’s crucial to pair SELECT TOP with the ORDER BY clause to ensure your results are sorted in a meaningful way. This will return the top 50 percent of the records, sorted by TotalAmount in descending order. Following the TOP keyword is an expression that specifies the number of rows to be returned. The expression is evaluated to a float value if PERCENT is used, otherwise, it is converted to a BIGINT value. You also learned to insert a limited amount of data using the TOP clause with the INSERT INTO statement.
The SELECT TOP clause is a SQL command used to limit the number of rows returned from a query. This is particularly useful when you’re dealing with large databases, and you want a subset of records. For example, you may want to fetch the top 10 performers in a sales team, the top 100 customers by sales volume, or the first 50 records from a table. The SELECT TOP clause allows you to limit the number of rows or percentage of rows returned in a query result set.
It’s imperative to note that using the TOP clause without the ORDER BY statement might give unpredictable results as databases don’t guarantee the order of rows by default. Sometimes we want to retrieve a specific number of records from the SQL table rather than retrieving all table records. We can use the WHERE clause in a SELECT statement to limit the number of rows. Suppose you have a customer table and you want to retrieve records for customers belonging to a particular country. Mastering the SQL TOP clause has the power to significantly boost your database querying skills. By efficiently retrieving only the data you need, you’ll save time and resources, making your applications run smoother.
