I am trying to answer the following question. Show ID_Number and name for the five lowest paid employees.
This is the table with employees:
CREATE TABLE Employees
(ID_No CHAR(4) NOT NULL,
Name VARCHAR(50) NOT NULL,
Hire_Date DATE NOT NULL,
Position VARCHAR(20) CHECK(Position IN('CHAIRMAN','MANAGER','ANALYST','DESIGNER','PROGRAMMER','SALES REP','ADMIN','ACCOUNTANT')),
Salary NUMERIC(8,2) NOT NULL,
Mgr_ID_No CHAR(4) NULL,
Dept_No SMALLINT NULL);
I will add that I've been trying a few methods and "limit" and "top" do not work for some reason.
In Oracle 12c :
-- more than 5 rows being returned, if multiple rows
-- match the value of the 5th row
SELECT e.ID_No, e.Name
FROM Employees e
ORDER BY e.Salary
FETCH FIRST 5 ROWS WITH TIES;
-- only 5 rows being returned, even if multiple rows
-- match the value of the 5th row
SELECT e.ID_No, e.Name
FROM Employees e
ORDER BY e.Salary
FETCH FIRST 5 ROWS ONLY;
-- NEXT clause may be replaced with FIRST
SELECT e.ID_No, e.Name
FROM Employees e
ORDER BY e.Salary
FETCH NEXT 5 ROWS ONLY;
Prior to Oracle 12c :
SELECT e.ID_No, e.Name
FROM ( SELECT ID_No, Name, row_number() over (order by salary) seq FROM Employees ) e
WHERE e.seq <= 5
ORDER BY e.seq;
queries may be used for Top-N Queries