SQL TOP Explained [Practical Examples]


SQL

Reviewer: Deepak Prasad

Overview of SQL TOP

SQL TOP clause is used to limit the records returned as the result set of SQL select query by specified number of rows or percentage of rows in SQL Server. When you use TOP with the ORDER BY clause, the result set is limited to the first N number of ordered rows. Otherwise, TOP returns the first N number of rows in an undefined order

All the database systems do not support the TOP keyword for selecting the limited number of records, Oracle supports the ROWNUM keyword, MySQL supports the LIMIT keyword and SQL Server uses SELECT TOP

 

SQL TOP Syntax

SELECT  TOP NUMBER| PERCENT   [ WITH TIES ] *|column1|expression ,column2|expression...
FROM table_name
[WHERE conditions]
[ ORDER BY sort_expression1 [ASC | DESC] [, sort_expression2 [ASC | DESC] ...] ]

Here

  • NUMBER: it is a numeric value that specifies the number of rows to be returned. The number value will be in bigint type, and if we specify percent it will be in float data type
  • PERCENT: it indicates that the query returns only the first number percent of records from the result set. Fractional values are rounded up to the next integer value
  • WITH TIES: Returns two or more rows that tie values that match the last row in the limited result set, with ties argument must be used with the ORDER BY clause. WITH TIES might cause more rows to be returned than the value specified in expression
  • Column1|expression : It is the list of columns or expressions to be retrieved
  •  Table_name: It is the name of the table from which we want to retrieve data, if we want to retrieve data from more than one table we can specify using SQL joins, There must be at least one table listed in the FROM clause.
  • WHERE conditions : It is an optional argument with SQL Select, the records which are satisfied this condition will be retrieved
  • ORDER BY expression: It is an optional argument with SQL select clause; it is used in the SELECT LIMIT statement so that you can order the results and target those records that you wish to return. ASC is ascending order and DESC in descending order

 

Examples of SQL TOP

Consider student result management system database with three tables student, result  and subject  to perform practical example on SQL TOP
Student Table

student_id studentname admissionno admissiondate enrollmentno date_of_birth email city class_id
101 reema 10001 02-02-2000 e15200002 02-02-1990 reema@gmail.com surat 2
102 kriya 10002 04-05-2001 e16200003 04-08-1991 kriya@gmail.com surat 1
103 meena 10003 06-05-1999 e15200004 02-09-1989 meena@gmail.com vadodara 3
104 carlin 2001 04-01-1998 e14200001 04-04-1989 carli@gmail.com vapi 1
105 dhiren 2002 02-02-1997 e13400002 02-02-1987 dhiru@gmail.com vapi 2
106 hiren 2003 01-01-1997 e13400001 03-03-1887 hiren@gmail.com surat 2
107 mahir 10004 06-09-2000 e15200003 07-09-1990 mahi@gmail.com vapi 3
108 nishi 2004 02-04-2001 e16200001 03-02-1991 nishi@gmail.com vadodara 1

Result Table

result_id student_id examname examdate subject obtainmark totalmarks percentage grade status
3001 101 sem1 07-08-2001 1 80 100 80 A+ pass
3002 101 sem1 08-08-2001 2 76 100 76 A+ pass
3003 102 sem3 05-05-2000 3 67 100 67 A pass
3004 102 sem3 06-05-2000 4 89 100 89 A+ pass
3005 102 sem3 07-05-2000 5 90 100 90 A+ pass
3006 103 sem5 08-09-1998 6 55 100 55 B pass
3007 103 sem5 09-09-1998 7 30 100 30 D fail
3008 103 sem5 10-09-1998 8 34 100 34 D fail

Subject Table

subjectid facultyname subjectname subjectcode
1 krishna c 1003
2 rahul cpp 1004
3 radha asp 1005
4 meera sql 1006
5 yasoda cloud 1007
6 nadan cg 1008

 

SQL TOP example to retrieve top three records

Example 1: Write SQL query to display top 3 records of student

SELECT TOP (3) student_id, studentname, admissionno, admissiondate, enrollmentno, date_of_birth, email, city, class_id
FROM tblstudent
In the above query, SQL TOP clause is used with SQL select statement to retrieve top 3 records from student table

OUTPUT:

SQL TOP Explained [Practical Examples]

 

SQL TOP with Order by

Example 2: Write SQL Query to display top two newly admission taken student information

SELECT TOP (2) student_id, studentname, admissionno, admissiondate, enrollmentno, date_of_birth, email, city, class_id FROM   tblstudent
ORDER BY admissiondate DESC
  • In the above query, SQL TOP clause is used with the number argument as 2 to retrieve top two newly admission student data
  • SQL order by clause is used to arrange the resulting record set in the descending order of admission date

OUTPUT:

SQL TOP Explained [Practical Examples]

Example 3: Write SQL query to retrieve top 3 minimum obtained marks student result data

SELECT TOP (3) result_id, student_id, examname, examdate, subjectid, obtainmark, totalmarks, pecentage, grade, status FROM  tblresult
ORDER BY obtainmark
  • In the above query, SQL TOP clause is used to fetch top 3 minimum obtain marks student’s result data
  • SQL order by clause is used with SQL select statement to order the resulting record set in the ascending order of obtaining marks

OUTPUT:

SQL TOP Explained [Practical Examples]

 

SQL TOP with the ORDER BY and JOIN

SQL TOP clause can also be used with SQL joins to retrieve combined records from two or more tables

Example 4: Write SQL query to retrieve two 4 highest getting marks student data with their result

SELECT TOP (4) tblstudent.student_id, tblstudent.studentname, tblstudent.admissionno, tblstudent.enrollmentno, tblstudent.email, tblresult.examname AS 'Semester', tblresult.examdate AS 'Exam Date', tblresult.obtainmark, tblresult.totalmarks, tblresult.pecentage, tblresult.grade, tblresult.status FROM  tblstudent 
LEFT OUTER JOIN tblresult ON tblstudent.student_id = tblresult.student_id
ORDER BY tblresult.obtainmark DESC
  • In the above query, SQL join with the order by clause is used to retrieve join records from two tables student and result in the descending order of obtaining marks of students
  • SQL TOP is used to limit the resulting recordset to four records, to fetch the top four highest obtainmark student data

OUTPUT:

SQL TOP Explained [Practical Examples]

 

SQL TOP PERCENT

Example 5: Write SQL query retrieve 50% of student data in the order of city name

SELECT TOP (50) PERCENT student_id, studentname, admissionno, admissiondate, enrollmentno, date_of_birth, email, city, class_id FROM   tblstudent
ORDER BY city
  • In the above query, SQL TOP PERCENT is used to limit the resulting record set to 50% of total records
  • SQL order by clause is used to arrange records in the ascending order of city name

OUTPUT:

SQL TOP Explained [Practical Examples]

 

SQL TOP PERCENT with Order by & Where condition

Example 6: Write SQL query to retrieve top 10% newly admission has taken student data of semester 5 with result information

SELECT TOP (10) PERCENT tblstudent.student_id, tblstudent.studentname, tblstudent.admissionno, tblstudent.admissiondate, tblstudent.enrollmentno, tblstudent.email, tblresult.examname AS 'Semester', tblresult.examdate AS 'Exam Date', tblresult.obtainmark, tblresult.totalmarks, tblresult.pecentage, tblresult.grade, tblresult.status FROM  tblstudent 
LEFT OUTER JOIN tblresult ON tblstudent.student_id = tblresult.student_id
WHERE (tblresult.examname = 'sem5')
ORDER BY tblstudent.admissiondate DESC
  • In the above query, SQL left join to join two tables record set student and result, SQL order by desc is used to arrange record set in descending order of admission date, SQL Where clause is used to conditionally fetch records as examname of sem5
  • SQL TOP PERCENT is restrict records to the top 10 percent of the total recordset

OUTPUT:

SQL TOP Explained [Practical Examples]

 

SQL TOP WITH TIES

Example 7: write SQL query to display top five student records in the order of their admission date also add the records with the same admission date as the last record of resulting recordset

SELECT TOP (5) WITH TIES student_id, studentname, admissionno, admissiondate, enrollmentno, date_of_birth, email, city, class_id 
FROM tblstudent
ORDER BY admissiondate
  • In the above query, SQL TOP WITH TIES is used to retrieve the top 5 records from the student table in the order of admission date
  • WITH TIES applied with a TOP clause to include the records with the same result if it is not added to top five records

OUTPUT:

SQL TOP Explained [Practical Examples]

 

SQL TOP Clause with SQL Delete statement

We can use TOP Clause in a SQL delete statement with the combination of SQL Select statement as the subquery with where clause

Example 8: Write SQL query to remove top 2 fail student data from the result table

DELETE FROM tblresult
WHERE (student_id IN
(SELECT TOP (2) student_id FROM  tblresult AS tblresult_1 ORDER BY obtainmark))  
  • In the above query, the SQL TOP clause is in the subquery to retrieve the top 2 student’s student IDs in the order of obtaining marks
  • The outer query of SQL delete will remove all records from the result table where result table student_id is matched with the student_id of the result of the subquery
  • when we execute the above query it shows a message like three rows has been deleted, as the result table contains three records that matched with the student ID of the subquery

OUTPUT:

To see the effect of above SQL delete query , we need to use SQL Select query to retrieve all records of result table

SELECT result_id, student_id, examname, examdate, subjectid, obtainmark, totalmarks, pecentage, grade, status 
FROM  tblresult

SQL TOP Explained [Practical Examples]

 

SQL TOP with SQL Update Statement

We can use SQL TOP Clause with  SQL Update statement to limit the records to be updated, we need to specify SQL TOP clause in the subquery with where condition in the SQL Update

Example 9: Write SQL query to update top 5 student’s class id to 1 who has recently take admission

UPDATE tblstudent SET class_id = 1
WHERE (student_id IN
(SELECT  TOP (5) student_id FROM  tblstudent AS tblstudent_1 ORDER BY admissiondate DESC))
  • In the above query, SQL TOP clause is used in sub query of SQL select statement to retrieve top 5 newly admission taken student data
  • The Outer query SQL Update will modify records of student table where student ID is matched with the resulting recordset student ID of subquery

OUTPUT:

To see the result of the above SQL Update statement, we need to used the SQL Select statement

SELECT student_id, studentname, admissionno, admissiondate, enrollmentno, date_of_birth, email, city, class_id 
FROM tblstudent

SQL TOP Explained [Practical Examples]

 

Summary

In this article, we have covered the overview of SQL TOP, Syntax with an explanation of each syntax argument, Practical examples on SQL TOP with a number, with the order by, with join, SQL TOP PERCENT, with where condition,WITH TIES, in SQL Delete and Update Statement

 

References

SQL OUTER JOIN

 

Read More

SQL TOP

 

Falguni Thakker

Falguni Thakker

She is a dedicated professional with expertise in SQL, Python, C++, and Linux. Currently serving as a professor at a prestigious university. With a passion for teaching and a strong technical background, she inspires the next generation of computer scientists. You can connect with her on her LinkedIn profile.

Can't find what you're searching for? Let us assist you.

Enter your query below, and we'll provide instant results tailored to your needs.

If my articles on GoLinuxCloud has helped you, kindly consider buying me a coffee as a token of appreciation.

Buy GoLinuxCloud a Coffee

For any other feedbacks or questions you can send mail to admin@golinuxcloud.com

Thank You for your support!!

Leave a Comment