How to find top three highest salary in emp table in oracle?
SELECT *FROM
(
SELECT *FROM emp
ORDER BY Salary desc
)
WHERE rownum <= 3
ORDER BY Salary ;
Nth max salary in Oracle
try this
select *
from
(
select
sal
,dense_rank() over (order by sal desc) ranking
from table
)
where ranking = 4 -- Replace 4 with any value of N
How to select the top 3 salaries of the department?
If you added a column Count employees who earn more
your table would look like this
+----+-------+--------+--------------+-------------------------------+
| Id | Name | Sa1ary | DepartmentId | Count employees who earn more |
+----+-------+--------+--------------+-------------------------------+
| 1 | Joe | 70000 | 1 | 2 |
| 2 | Henry | 80000 | 2 | 0 |
| 3 | Sam | 60000 | 2 | 1 |
| 4 | Max | 90000 | 1 | 0 |
| 5 | Janet | 69000 | 1 | 3 |
| 6 | Randy | 85000 | 1 | 1 |
+----+-------+--------+--------------+-------------------------------+
Then to find the top 3 per dept. your WHERE would be
WHERE `Count employees who earn more` < 3
If you had =3
it would return the only employees that was the 4th highest
Since you don't have that column, that's what this SQL does
(SELECT COUNT(DISTINCT(Salary)) FROM Employee
WHERE DepartmentId = E.DepartmentId AND Salary > E.Salary)
If you wanted to produce the table described above you could do the following
SELECT
D.Name AS Department,
E.Name AS Employee,
E.Salary AS Salary,
Count(E2.Salary) as Count_employees_who_earn_more
FROM Employee E
INNER JOIN Department D
ON E.DepartmentId = D.Id
LEFT JOIN Employee E2 ON
e2.DepartmentId = E.DepartmentId
AND E2.Salary > E.Salary
GROUP BY D.Name ,
E.Name ,
E.Salary
Demo
ORACLE sql query for getting top 3 salaries rownum greater than
a_horse_with_no_name's answer is a good one,
but just to make you understand why you're 1st query works and your 2nd doesn't:
When you use the subquery, Oracle doesn't magically use the rownum of the subquery, it just gets the data ordered so it gives the rownum accordingly, the first row that matches criteria still gets rownum 1 and so on. This is why your 2nd query still returns no rows.
If you want to limit the starting row, you need to keep the subquery's rownum, ie:
SELECT *
FROM (SELECT * , rownum rn
FROM (SELECT salary, first_name
FROM employees
ORDER BY salary ) )sq
WHERE sq.rn >= 3;
But as a_horse_with_no_name said there are better options ...
EDIT: To make things clearer, look at this query:
with t as (
select 'a' aa, 4 sal from dual
union all
select 'b' aa, 1 sal from dual
union all
select 'c' aa, 5 sal from dual
union all
select 'd' aa, 3 sal from dual
union all
select 'e' aa, 2 sal from dual
order by aa
)
select sub.*, rownum main_rn
from (select t.*, rownum sub_rn from t order by sal) sub
where rownum < 4
note the difference between the sub rownum and the main rownum, see which one is used for criteria
How to find third or nᵗʰ maximum salary from salary table?
Use ROW_NUMBER
(if you want a single) or DENSE_RANK
(for all related rows):
WITH CTE AS
(
SELECT EmpID, EmpName, EmpSalary,
RN = ROW_NUMBER() OVER (ORDER BY EmpSalary DESC)
FROM dbo.Salary
)
SELECT EmpID, EmpName, EmpSalary
FROM CTE
WHERE RN = @NthRow
Related Topics
SQL Different Between Left Join On... and Left Join On..Where
MySQL - Select All Except What Is in This Table
How to Find the Size of an Array in Postgresql
Sql- Ignore Case While Searching for a String
How to Delete Duplicates from a Database Table Based on a Certain Field
SQL Server Max Statement Returns Multiple Results
Select Newest Record Group by Username in SQL Server 2008
Why Would Year Fail with a Conversion Error from a Date
Convert Unknown Number of Comma Separated Varchars Within 1 Column into Multiple Columns
Select Something That Has More/Less Than X Character
Ukrainian Character Change to Question Mark When Insert to Table
SQL Server 2008 Unique Column That Is Case Sensitive
How to Find Least Non-Null Column in One Particular Row in SQL
Fastest Way to Insert in Parallel to a Single Table
Correct Way to Select from Two Tables in SQL Server with No Common Field to Join On