SQL - Top N

In SQL Server, the TOP clause is used to limit the number of rows returned by a query. It allows you to specify the maximum number of rows you want to retrieve from a result set. Here's the syntax for using the TOP clause:

SELECT TOP (N) column1, column2, ...
FROM table_name
ORDER BY column1, column2, ...;

In the above syntax, N represents the number of rows you want to retrieve, column1, column2, ... represent the columns you want to select, and the ORDER BY clause is optional. If you want the result set to be ordered, you can specify one or more columns in the ORDER BY clause.

For example, let's say you have a table called "Employees" with columns such as "EmployeeID", "FirstName", "LastName" and "Email":

Now, let's use the TOP clause to retrieve the top 5 customers based on their first names in alphabetical order:

SELECT TOP 5
    CustomerID,
    FirstName,
    LastName,
    Email
FROM
    Customers
ORDER BY
    FirstName;

The output result of this query would be:

In this result, you can see that it includes the top 5 customers based on their first names in alphabetical order. The TOP 5 clause limited the result set to only those 5 rows, and they are sorted by the "FirstName" column.

It's important to note that if you specify the TOP clause without an ORDER BY clause, the result set will be returned in an arbitrary order. To ensure a consistent order, you should always use the ORDER BY clause when using the TOP clause.

Additionally, you can use the TOP clause with a percentage value instead of an absolute number. For example, SELECT TOP (10 PERCENT) ... will retrieve the top 10 percent of rows from the result set.

Lastly, starting from SQL Server 2012, you can also use the OFFSET-FETCH clause to achieve similar functionality to the TOP clause. The OFFSET-FETCH clause allows you to skip a specified number of rows and fetch a specified number of rows from the result set. This can be useful for pagination purposes.