SQL Top Clause
Top Clause:
It returns the first ‘n’ number of rows as output. This Top clause is used to retrieve the required number of rows when there are thousands of records stored in the database.
SELECT TOP clause returns the limited number of rows as output.
All databases do not support the TOP clause, Hence the Oracle database uses the ROWNUM clause instead of the TOP clause. The Mysql database uses the LIMIT clause instead of the TOP clause.
The syntax for TOP Clause:
Select TOP number | Percent column1, column2, column3, ……, column from Table_name where ( condition);
Example:
Student Table
| sid | sname | sage | sgender | Phonenumber |
| 1 | Abhinav | 22 | Male | 9895678909 |
| 2 | Ramya | 24 | Female | 6687654634 |
| 3 | Preetham | 21 | Male | 9867546453 |
| 4 | Nethranand | 21 | Male | 7675643423 |
| 5 | Naveen | 23 | Male | 6567784532 |
| 6 | Harshita | 22 | Female | 9867546231 |
| 7 | Bindu | 26 | Female | 6563412768 |
| 8 | Nandhini | 23 | Female | 6785674839 |
| 9 | Hashish | 22 | Male | 9453215052 |
| 10 | Rahul | 21 | Male | 9998989898 |
1.Display the first three rows of the Student table with sid and sname
Code:
Select TOP 3 sid, sname from Student;
Output:
| sid | sname |
| 1 | Abhinav |
| 2 | Ramya |
| 3 | Preetham |
2. Display the Student table using percent i.e, 50 percent with all Student details.
Code:
Select TOP 50 PERCENT * from Student;
Output:
| sid | sname | sage | sgender | Phonenumber |
| 1 | Abhinav | 22 | Male | 9895678909 |
| 2 | Ramya | 24 | Female | 6687654634 |
| 3 | Preetham | 21 | Male | 9867546453 |
| 4 | Nethranand | 21 | Male | 7675643423 |
| 5 | Naveen | 23 | Male | 6567784532 |
3. Display the first eight rows of table whose gender is Female with student details:
Code:
Select TOP 8 * from Student where sgender = ‘Female’;
Output:
| sid | sname | sage | sgender | Phonenumber |
| 2 | Ramya | 24 | Female | 6687654634 |
| 6 | Harshita | 22 | Female | 9867546231 |
| 7 | Bindu | 26 | Female | 6563412768 |
| 8 | Nandhini | 23 | Female | 6785674839 |
ROWNUM in Oracle:
It returns the first ‘n’ number of rows as output. This clause is used to retrieve the required number of rows when there are thousands of records stored in the database.
The syntax for ROWNUM:
SELECT column1, column2, column3, ……, column from Table_name where ROWNUM <= value;
Example:
Student Table
| sid | sname | sage | sgender | Phonenumber |
| 1 | Abhinav | 22 | Male | 9895678909 |
| 2 | Ramya | 24 | Female | 6687654634 |
| 3 | Preetham | 21 | Male | 9867546453 |
| 4 | Nethranand | 21 | Male | 7675643423 |
| 5 | Naveen | 23 | Male | 6567784532 |
| 6 | Harshita | 22 | Female | 9867546231 |
| 7 | Bindu | 26 | Female | 6563412768 |
| 8 | Nandhini | 23 | Female | 6785674839 |
| 9 | Hashish | 22 | Male | 9453215052 |
| 10 | Rahul | 21 | Male | 9998989898 |
1.Display the first three rows of the Student table with sid and sname
Code:
SELECT sid, sname from Student where ROWNUM <= 3;
Output:
| sid | sname |
| 1 | Abhinav |
| 2 | Ramya |
| 3 | Preetham |
2. Display the Student table using top 5 rows with all Student details.
Code:
SELECT * from Student where ROWNUM <= 5;
Output:
| sid | sname | sage | sgender | Phonenumber |
| 1 | Abhinav | 22 | Male | 9895678909 |
| 2 | Ramya | 24 | Female | 6687654634 |
| 3 | Preetham | 21 | Male | 9867546453 |
| 4 | Nethranand | 21 | Male | 7675643423 |
| 5 | Naveen | 23 | Male | 6567784532 |
3. Display the first eight rows of the table whose gender is Female with student details:
Code:
SELECT * from (SELECT * from Student where ROWNUM <= 8) where sgender = ‘Female’;
Output:
| sid | sname | sage | sgender | Phonenumber |
| 2 | Ramya | 24 | Female | 6687654634 |
| 6 | Harshita | 22 | Female | 9867546231 |
| 7 | Bindu | 26 | Female | 6563412768 |
| 8 | Nandhini | 23 | Female | 6785674839 |
LIMIT in Mysql:
It returns the first ‘n’ number of rows as output. This clause is used to retrieve the required number of rows when there are thousands of records stored in the database.
The syntax for LIMIT clause:
SELECT column1, column2, column3, ……, column from Table_name LIMIT value;
Example:
Student Table
| sid | sname | sage | sgender | Phonenumber |
| 1 | Abhinav | 22 | Male | 9895678909 |
| 2 | Ramya | 24 | Female | 6687654634 |
| 3 | Preetham | 21 | Male | 9867546453 |
| 4 | Nethranand | 21 | Male | 7675643423 |
| 5 | Naveen | 23 | Male | 6567784532 |
| 6 | Harshita | 22 | Female | 9867546231 |
| 7 | Bindu | 26 | Female | 6563412768 |
| 8 | Nandhini | 23 | Female | 6785674839 |
| 9 | Hashish | 22 | Male | 9453215052 |
| 10 | Rahul | 21 | Male | 9998989898 |
1.Display the first three rows of the Student table with sid and sname
Code:
SELECT sid, sname from Student LIMIT 3;
Output:
| sid | sname |
| 1 | Abhinav |
| 2 | Ramya |
| 3 | Preetham |
2. Display the Student table using with all Student details of top 5 rows.
Code:
SELECT * from Student LIMIT 5;
Output:
| sid | sname | sage | sgender | Phonenumber |
| 1 | Abhinav | 22 | Male | 9895678909 |
| 2 | Ramya | 24 | Female | 6687654634 |
| 3 | Preetham | 21 | Male | 9867546453 |
| 4 | Nethranand | 21 | Male | 7675643423 |
| 5 | Naveen | 23 | Male | 6567784532 |
3. Display the first eight rows of table whose gender is Female with student details:
Code:
SELECT * from ( SELECT * from Student LIMIT 8) where sgender = ‘Female’;
Output:
| sid | sname | sage | sgender | Phonenumber |
| 2 | Ramya | 24 | Female | 6687654634 |
| 6 | Harshita | 22 | Female | 9867546231 |
| 7 | Bindu | 26 | Female | 6563412768 |
| 8 | Nandhini | 23 | Female | 6785674839 |