SELECT TOP command in SQL Server

SELECT TOP in SQL Server What is that? You can select top 1 in SQL Server easily if you know how to use this command. The following are detailed instructions.

While retrieving data from a SQL table, the LIMIT clause is used to limit the number of rows in the result group. Similarly, if you implement tasks like update, discovery, etc., it is used to limit the target records.

SQL Server does not support the LIMIT clause. Instead, it provides the TOP statement in SQL Server to limit the number of rows in a table. TOP function in SQL Server Similar to the LIMIT command.

The SELECT TOP command is useful when you need to operate on large tables with thousands of records to retrieve the required value. In such cases, returning a large number of records can impact performance.

Note, not all database systems allow using the TOP command to select a limited number of records. For example, MySQL supports the LIMIT command, while Orcale supports the ROWNUM command.

Below are details on how to use SELECT TOP in SQL Server.

In SQL Server, the SELECT TOP command is used to retrieve records from one or more tables in SQL Server and limit the number of records returned based on a fixed value or percentage.

Syntax of SELECT TOP command

SELECT TOP (giatri_dau) [PERCENT] [WITH TIES]
bieu_thuc
FROM bang
[WHERE dieu_kien]
[ORDER BY bieu_thuc [ ASC | DESC ]];

Variable name or variable value

TOP (giatri_dau)

Returns results based on giatri_dau. For example, TOP(10) will insert the first 10 rows from the result set.

PERCENT

Options. If specifically stated, the first rows are based on the percentage value of the result set. For example, TOP(10) PERCENT will insert the top 10% of values ​​in the result set.

WITH TIES

Options. If this clause is used, rows with the same value as the last row in the result set will be returned. This can cause a situation where there are more rows returned than the TOP variable allows.

symbol

Column or calculated value to be retrieved

state

The table wants to get the record from. There must be at least 1 table in the FROM clause.

WHERE conditions

Options. The condition must be met for the record to be selected.

ORDER BY symbol

Options. Used to order results. ASC in ascending order, DESC in descending order.

Compatibility support

With backward compatibility, parentheses are optional in the SELECT statement if the expression is an integer constant. You should use parentheses for TOP in SELECT statements. Doing so will ensure consistency while using the necessary INSERT, UPDATE, MERGE, and DELETE statements.

Limit

When using TOP with INSERT, UPDATE, MERGE, or DELETE, the referenced rows are not sorted. And you cannot directly specify an ORDER BY clause in these statements. If you need to use TOP to insert, delete or edit rows in the necessary chronological order, use TOP with the ORDER BY clause specified in the sub-command.

You cannot use TOP in UPDATE and DELETE commands on partitioned views. You also cannot combine TOP with OFFSET and FETCH in the same query expression.

Example of how to use SELECT TOP in SQL SERVER

For example – use the keyword TOP

SELECT TOP(5)
nhanvien_id, ho, ten
FROM nhanvien
WHERE ho = ‘Anderson’
ORDER BY nhanvien_id;

The above example will retrieve the first 5 records on the Nhanvien table when the last name is Anderson. If other records also have the employee last name Anderson, they will not be returned in the above SELECT command.

The above example can be modified a bit by adding a WITH TIES clause

SELECT TOP(5) WITH TIES
nhanvien_id, ho, ten
FROM nhanvien
WHERE ho = ‘Anderson’
ORDER BY nhanvien_id;

This example will return rows that are identical to the last row in the result set.

For example – use the keyword TOP PERCENT

SELECT TOP(10) PERCENT 
nhanvien_id, ho, ten
FROM nhanvien
WHERE ho = ‘Anderson’
ORDER BY nhanvien_id;

This example will return a result set of the first 10% of records in the employees table among employees whose last name is Anderson. The remaining 90% will not be returned.

SELECT TOP(10) PERCENT WITH TIES 
nhanvien_id, ho, ten
FROM nhanvien
WHERE ho = ‘Anderson’
ORDER BY nhanvien_id;

With WITH TIES, the returned results will have the same rows as the last row in the result set. The result set will then be 10% larger.

Some common uses of the SQL Server TOP clause

• Pagination – When displaying a large number of records, the TOP clause can be used to return only certain records at a time, allowing for more manageable and efficient data paging.
• Data sampling – The TOP clause can be used to quickly sample data from a table for inspection or analysis.
• Improved performance – By limiting the number of returned functions, the TOP command can help improve query efficiency, especially when processing large data tables.
• Debugging – When developing or debugging a query, the TOP command can be used to quickly return a small number of rows to check the accuracy of the query.
• Data visualization – The TOP command can be used to limit the number of rows returned for visualization purposes such as creating charts and graphs.

Previous article: HAVING clause in SQL Server

Next article: SELECT INTO command in SQL Server