You could use DBMS_STAT_FUNCS.Summary:
SET SERVEROUTPUT ON;
DECLARE
s DBMS_STAT_FUNCS.SummaryType;
BEGIN
DBMS_STAT_FUNCS.SUMMARY('HR', 'EMPLOYEES', 'SALARY',3, s);
DBMS_OUTPUT.put_line('Top 3: ' || s.top_5_values(1) || '-'
|| s.top_5_values(2) || '-' || s.top_5_values(3));
END;
/
Output:
Top 3: 24000-17000-17000
SELECT * FROM Employees
WHERE rownum <= 3
ORDER BY Salary ;
SELECT Name, Salary
FROM
(
SELECT Name, Salary
FROM emp
ORDER BY Salary desc
)
WHERE rownum <= 3
ORDER BY Salary ;
You can try.
SELECT * FROM
(
SELECT EMPLOYEE, LAST_NAME, SALARY,
RANK() OVER (ORDER BY SALARY DESC) EMPRANK
FROM emp
)
WHERE emprank <= 3;
This will give correct output even if there are two employees with same maximun salary
SELECT salary,first_name||' '||last_name "Name of the employee"
FROM hr.employees
WHERE rownum <= 3
ORDER BY salary desc ;
Limit The Query To Display Only The Top 3 Highest Paid Employees. : Query « Oracle PL / SQL
create table employee(
emp_no integer primary key
,lastname varchar2(20) not null
,salary number(3)
);
insert into employee(emp_no,lastname,salary)
values(1,'Tomy',2);
insert into employee(emp_no,lastname,salary)
values(2,'Jacky',3);
insert into employee(emp_no,lastname,salary)
values(3,'Joey',4);
insert into employee(emp_no,lastname,salary)
values(4,'Janey',5);
select lastname, salary
from (SELECT lastname, salary FROM employee ORDER BY salary DESC)
where rownum <= 3 ;
OUTPUT
LASTNAME SALARY
-------------------- ----------
Janey 5
Joey 4
Jacky 3
drop table employee;
Something like the following should do it.
SELECT Name, Salary
FROM
(
SELECT Name, Salary
FROM emp
ORDER BY Salary desc
)
WHERE rownum <= 3
ORDER BY Salary ;
SELECT DISTINCT(salary) FROM emp order by salary asc limit 0 ,3
Above query gives three highest salary with DISTINCT.
Select ename, job, sal from emp
where sal >=(select max(sal) from emp
where sal < (select max(sal) from emp
where sal < (select max(sal) from emp)))
order by sal;
ENAME JOB SAL
---------- --------- ----------
KING PRESIDENT 5000
FORD ANALYST 3000
SCOTT ANALYST 3000
select top(3) min(Name),TotalSalary,ROW_NUMBER() OVER (Order by TotalSalary desc) AS RowNumber FROM tbl_EmployeeProfile group by TotalSalary
select empno,salary from emp e
where 3 > ( Select count(salary) from emp
where e.salary < salary )
Another way :
select * from
(
select empno,salary,
Rank() over(order by salary desc) as rank from emp )
where Rank <= 3;
Another Way :
select * from
(
select empno,salary from emp
order by salary desc
)
where rownum <= 3;
solution for to find top 5 salary in sq l server
select top(1) name, salary from salary where salary in(select distinct top(3) salary from salary order by salary disc)
SELECT * FROM
(
SELECT EMPLOYEE, LAST_NAME, SALARY,
DENSE_RANK() OVER (ORDER BY SALARY DESC) EMPRANK
FROM emp
)
WHERE emprank <= 3;
SELECT * FROM
(
SELECT ename, sal,
DENSE_RANK() OVER (ORDER BY SAL DESC) EMPRANK
FROM emp
)
emp1 WHERE emprank <=5
SELECT a.ename, b.sal
FROM emp a, emp b
WHERE a.empno = b.empno
AND
3 > (SELECT count(*) FROM emp b
WHERE a.sal = b.sal);
Without using TOP, ROWID, rank etc. Works with oldest sql also
select top 3 * from emp order by sal desc
Source: Stackoverflow.com