Limiting Rows in SQL

         In this article I am going to explain how limit the rows in the result in a SELECT query.

Top Clause

        TOP clause is used to limit the result set with N number of rows or X percentage of rows.

We can use the TOP clause in four ways, below I have given the syntax for each of them.

Syntax

    1. SELECT TOP (N) <column_list> FROM table_name

       This will return top N number of rows from the table

    2. SELECT TOP (X) PERCENT <column_list> FROM table_name

       This will return top X percentage of rows from the table

    3. SELECT TOP (N) WITH TIES  <column_list> FROM table_name

        We will see more detail about the 'WITH TIES' later in this post.

    4. SELECT TOP (X) PERCENT WITH TIES <column_list> 

          FROM talbe_name ORDER BY  <column_list>

Explanation

         TOP (N) will return the top n number of rows. It will take records from the top of the table.

Let's create and populate a table to see how TOP clause works.

CREATE TABLE People(

    NAME VARCHAR(50)

    ,Age TINYINT

    ,City VARCHAR(50)

)

GO

 

INSERT INTO People

VALUES('Rajesh',23,'Delhi')

,('Kumar',21,'Mumbai')

,('Joe',22,'Chennai')

,('Kathir',22,'Mumbai')

,('Ram',24,'Chennai')

,('Angel',22,'Chennai')

,('Jessy',20,'Chennai')

,('Litsa',22,'Delhi')

      WITH TIES will return all the rows which contains the last value returned by the ORDER BY clause

      

      The above query with 'WITH TIES' returned 6 rows instead of 3 rows.Because when you use 'WITH TIES' it will select the top 3 rows (in this case), then it will look in to the last value of the column specified in the ORDER BY clause. 

       In this example I have used 'Age' in the ORDER BY clause. So the 3rd row has the age value 22, then the SQL Server return all the other rows which have Age 22 even the number of rows exceed than we specified in the TOP clause

 Note: ORDER BY clause is mandatory to use WITH TIES