SQL TOP Clause

The SQL provides us with the option to retrieve a specific set of data from the table, rather than fetching all the table or only some specific rows by mentioning the matching condition in the WHERE clause.

Now what if you want to get the top 10 rows or top 5 rows from the table, or you simply want to retrieve the last, middle number of rows. But the top clause is not supported by all DBMS, some have respective syntax. However the SQL Limit can also act in the same way. For Oracle equivalent is Rownum.

Syntax

SELECT TOP (n) column_name(s) FROM TABLE_NAME

Consider the following table

Firstname	Lastname	Age	Marital Status	Country
John	Smith	40	Married	Usa
Mathew	Simon	30	Married	Uk
Bill	Steve	20	Single	Usa
Amanda	Rogers	28	Married	Germany
Steve	Hills	30	Single	France

Example

SELECT TOP 3 Firstname, Lastname, Age FROM Users

Result of the Query

Firstname	Lastname	Age
John	Smith	40
Mathew	Simon	30
Bill	Steve	20

Example With Percentage

SELECT TOP 40% Firstname, Lastname, Age FROM users

Result of the Query

Firstname	Lastname	Age
John	Smith	40
Mathew	Simon	30

The percentage work like the normal mathematical calculation, that is the only 40% of the total number of rows in the table are fetched.