How to use Select Top in Sql Server

Intro

SQL Server provides an additional statement to limit rows, SELECT TOP. This statement allows you to specify the number of rows or percentage of rows to return from an ordered sql query. In this article, we will learn how to use SELECT TOP in SQL Server.

The Syntax

The basic syntax of a SELECT TOP is as follows:

SELECT TOP (expression) [PERCENT]
    [WITH TIES]
FROM 
    table_name
ORDER BY 
    column_name;

Getting Setup

For this, we will be using docker. This is recommended for more than just using SQL Server. To find how to install docker go here: https://docs.docker.com/engine/install/

Now create a file called docker-compose.yml and add the following.

version: "3.9"
services:
  db:
    image: "mcr.microsoft.com/mssql/server"
    ports: 
      - 1433:1433
    environment:
        SA_PASSWORD: "Your_password123"
        ACCEPT_EULA: "Y"

Open a terminal and go to the folder the file is located. Then run the following.

docker-compose up

If you are looking for another good reference, you can check here: https://docs.docker.com/samples/aspnet-mssql-compose/.

Example

Let's start by creating a simple Employee table.

CREATE TABLE employees (
    first_name VARCHAR (50) NOT NULL,
    last_name VARCHAR (50) NOT NULL,
);

Now we can insert some employees;

insert into employees (first_name, last_name) 
	values 
	('keith', 'holliday'),
	('jon', 'doe'),
	('jon', 'dane'),
	('jane', 'done'),
	('anna', 'smith');

In this first example, let's select the first 2 rows from our employees when sorting by first name descending.

SELECT TOP 2
  	*
FROM
    employees e
ORDER BY 
    first_name DESC;

Next, let's do the same thing, but instead of using a static number, let's use a percent.

SELECT TOP 40 PERCENT
  	*
FROM
    employees e
ORDER BY 
    first_name DESC;

One final thing we can do is use the WITH TIES clause which will add more rows if there is a tie.

Since our data has two Jon's. Both will be shown if we add WITH TIES to the previous query.

SELECT TOP 40 PERCENT WITH TIES
  	*
FROM
    employees e
ORDER BY 
    first_name DESC;