Skip to main content

SQL TOP Clause

Body

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.

Add new comment