Open In App

Select top in MS SQL Server

Improve
Improve
Improve
Like Article
Like
Save Article
Save
Share
Report issue
Report

Prerequisite – Select in MS SQL Server
Suppose that a user wants to extract the top students from the whole institution but has to use some complex queries to extract the data. To avoid complexity, the user can use ‘Select Top’.
‘Select Top’ extracts the limited number of rows. This results in accurate data along with less time consumption.

Syntax –

select top (expression) [percent] [with ties]
from table_name 
order by column_name 

Analyzing the Syntax –

  • Top is a keyword that extracts the data from the top of the list.
  • Expression is the data that is to be extracted from the table.
  • Percent is the number of rows that need to be extracted from the table.
  • With Ties returns the rows that share the same values with the last row. In some cases, more rows can be retrieved.

The order by clause is used for arranging the data in a chronological order. It is mandatory to use this clause in syntax otherwise, it results in an error.

Example –
If a user wants to extract the top 5 students of an institution, the query is written as –

select top 5 name rollnumber gpa
from student 
order by name ASC

Output –

Roll number Name GPA
114 Aisha 9.5
116 Apoorva 9.4
119 Mina 8.7
114 Rita 8.1
118 Veena 7.7

This way the desired data can be extracted. The last row student has a gpa of 7.7 and if there are a few more students that share the same numbers, the query must be written as –

select top 8 with ties
name rollnumber gpa
from student
order by name ASC

Output –

Roll number Name GPA
114 Aisha 9.5
116 Apoorva 9.4
119 Mina 8.7
114 Rita 8.1
118 Veena 7.7
110 Vinitha 7.7
101 Yamini 7.7
107 Zubaida 7.7

ASC arranges the data from ascending to descending order. DESC can be used if the data has to be arranged from descending to ascending order.


Last Updated : 09 Jul, 2020
Like Article
Save Article
Previous
Next
Share your thoughts in the comments
Similar Reads