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 | 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 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:
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 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 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 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 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 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 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
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
Read More