What Does “Last 5 Records” Mean in SQL?

Unlike Excel, SQL tables have no inherent order. So when we say “last 5 records”, we must define it based on a column — for example:

• Latest 5 inserted rows → using created_date or id
• Highest 5 salaries → using salary

Sample Table: employees

CREATE TABLE employees (
  id NUMBER PRIMARY KEY,
  name VARCHAR2(50),
  salary NUMBER,
  created_at DATE
);

-- Sample data
INSERT INTO employees VALUES (1, 'Alice', 5000, TO_DATE('2023-01-01', 'YYYY-MM-DD'));
INSERT INTO employees VALUES (2, 'Bob', 7000, TO_DATE('2023-02-15', 'YYYY-MM-DD'));
INSERT INTO employees VALUES (3, 'Charlie', 6500, TO_DATE('2023-03-10', 'YYYY-MM-DD'));
INSERT INTO employees VALUES (4, 'Diana', 6000, TO_DATE('2023-04-05', 'YYYY-MM-DD'));
INSERT INTO employees VALUES (5, 'Eve', 7200, TO_DATE('2023-05-01', 'YYYY-MM-DD'));
INSERT INTO employees VALUES (6, 'Frank', 5500, TO_DATE('2023-06-20', 'YYYY-MM-DD'));
COMMIT;

Methods to Read Last 5 Records

Method 1: Using ORDER BY with FETCH (Oracle 12c+)

Get Last 5 by id (most recent entries):

SELECT *
FROM employees
ORDER BY id DESC
FETCH FIRST 5 ROWS ONLY;

This gives the last 5 rows by id. You can change id to any other column like created_at.

To return the result in ascending order (oldest of the 5 first):

SELECT *
FROM (
  SELECT * FROM employees ORDER BY id DESC FETCH FIRST 5 ROWS ONLY
)
ORDER BY id ASC;

Method 2: Using Subquery + ROWNUM (for Oracle 11g and below)

SELECT *
FROM (
  SELECT * FROM employees ORDER BY id DESC
)
WHERE ROWNUM <= 5;

To return in ascending order of id:

SELECT *
FROM (
  SELECT * FROM (
    SELECT * FROM employees ORDER BY id DESC
  ) WHERE ROWNUM <= 5
)
ORDER BY id ASC;

Method 3: Using ROW_NUMBER() Analytic Function (Flexible for Pagination)

SELECT *
FROM (
  SELECT e.*, ROW_NUMBER() OVER (ORDER BY id DESC) AS rn
  FROM employees e
)
WHERE rn <= 5
ORDER BY id ASC;

You can replace id with any column like created_at, salary, etc., depending on your use case.

Example Output (Last 5 by ID):

Assuming employees has:

ID	NAME	SALARY	CREATED_AT
1	Alice	5000	2023-01-01
2	Bob	7000	2023-02-15
3	Charlie	6500	2023-03-10
4	Diana	6000	2023-04-05
5	Eve	7200	2023-05-01
6	Frank	5500	2023-06-20

Output for “Last 5 Records” by ID:

ID	NAME	SALARY	CREATED_AT
2	Bob	7000	2023-02-15
3	Charlie	6500	2023-03-10
4	Diana	6000	2023-04-05
5	Eve	7200	2023-05-01
6	Frank	5500	2023-06-20

Summary Table

Method	Oracle Version	Sort Required?	Supports Pagination
FETCH FIRST ... ROWS ONLY	12c+	Yes	Yes
Subquery + ROWNUM	All	Yes	Manual Only
ROW_NUMBER() Analytic Function	10g+	Yes	Yes