In Oracle using ROWNUM :
For each row returned by a query, the ROWNUM pseudocolumn returns a number indicating the order in which Oracle selects the row from a table or set of joined rows. The first row selected has a ROWNUM of 1, the second has 2, and so on.
You can use ROWNUM to limit the number of rows returned by a query, as in this example:
SELECT Cols |* FROM table_name WHERE ROWNUM < Number;
Example to find the 3rd highest salary using Oracle Database:
In SQL using SELECT TOP clause :
The SELECT TOP clause is used to specify the number of records to return, as in this example:
SELECT TOP number|percent column_name(s) FROM table_name;
Example to find the 3rd highest salary using MySql Database:
Query 1: SELECT MIN(SALARY) AS Salary FROM Employee WHERE SALARY IN (SELECT DISTINCT TOP 3 SALARY FROM Employee ORDER BY SALARY DESC);
Query 2 : SELECT Top 1 Salary AS Salary FROM Employee WHERE SALARY IN (SELECT DISTINCT TOP 3 SALARY FROM Employee ORDER BY SALARY DESC) ORDER BY SALARY;
Also Read : SQL : Find 2nd or 3rd highest salary from employee table without ROWNUM or TOP
For each row returned by a query, the ROWNUM pseudocolumn returns a number indicating the order in which Oracle selects the row from a table or set of joined rows. The first row selected has a ROWNUM of 1, the second has 2, and so on.
You can use ROWNUM to limit the number of rows returned by a query, as in this example:
SELECT Cols |* FROM table_name WHERE ROWNUM < Number;
Example to find the 3rd highest salary using Oracle Database:
Employee Table :
Sql Command in action in Oracle Database
The SELECT TOP clause is used to specify the number of records to return, as in this example:
SELECT TOP number|percent column_name(s) FROM table_name;
Example to find the 3rd highest salary using MySql Database:
Query 1: SELECT MIN(SALARY) AS Salary FROM Employee WHERE SALARY IN (SELECT DISTINCT TOP 3 SALARY FROM Employee ORDER BY SALARY DESC);
Query 2 : SELECT Top 1 Salary AS Salary FROM Employee WHERE SALARY IN (SELECT DISTINCT TOP 3 SALARY FROM Employee ORDER BY SALARY DESC) ORDER BY SALARY;
Also Read : SQL : Find 2nd or 3rd highest salary from employee table without ROWNUM or TOP
0 comments:
Post a Comment