SQL SELECT FETCH AND ROWNUM

Write SQL select FETCH queries.

The SELECT FETCH and ROWNUM clause is used to specify the number of records to return

Example


Table: Employee                            
+----+-------+--------+--------------+     
| Id | Name  | Salary | DepartmentId |     
+----+-------+--------+--------------+   
1  | Ram   | 85000  | 1            |   
2  | Henry | 80000  | 2            |   
3  | Sam   | 60000  | 2            |   
4  | Shyam | 60000  | 1            |
5  | Geeta | 90000  | 1            |
6  | Sheet | 90000  | 1            |
7  | Leela | 80000  | 1            |
8  | Geeta | 70000  | 1            |
+----+-------+--------+--------------+

Approach

Oracle: SELECT TWO 2 records


SELECT * FROM Employee FETCH FIRST 2 ROWS ONLY;

Result:

+----+-------+--------+--------------+     
| Id | Name  | Salary | DepartmentId |     
+----+-------+--------+--------------+   
1  | Ram   | 85000  | 1            |   
2  | Henry | 80000  | 2            |
+----+-------+--------+--------------+

Oracle: SELECT 2 records with WHERE condition


SELECT  * FROM Employee WHERE DepartmentID=1 AND ROWNUM <=2;

Result:

+----+-------+--------+--------------+     
| Id | Name  | Salary | DepartmentId |     
+----+-------+--------+--------------+   
1  | Ram   | 85000  | 1            |   
4  | Shyam | 60000  | 1            |
+----+-------+--------+--------------+


No comments:

Post a Comment