ORACLE - ROW_NUMBER, FIRST_VALUE, LAST_VALUE

In this post I’ll explain the usage of the SQL functions ROW_NUMBER, FIRST_VALUE and LAST_VALUE.

ROW_NUMBER

It assigns a unique number to each row to which it is applied, in the ordered sequence of rows specified in the order_by_clause, beginning with 1.

Syntax:

ROW_NUMBER( )
   OVER ([ query_partition_clause ] order_by_clause)

FIRST_VALUE

It returns the first value in an ordered set of values. If the first value in the set is null, then the function returns NULL unless you specify IGNORE NULLS.If you specify IGNORE NULLS, then FIRST_VALUE returns the fist non-null value in the set, or NULL if all values are null.

Syntax:

FIRST_VALUE (expr [ IGNORE NULLS ])
   OVER (analytic_clause)

LAST_VALUE

It returns the last value in an ordered set of values. If the last value in the set is null, then the function returns NULL unless you specify IGNORE NULLS. If you specify IGNORE NULLS, then LAST_VALUE returns the fist non-null value in the set, or NULL if all values are null.

Syntax:

LAST_VALUE(expr [ IGNORE NULLS ])
   OVER (analytic_clause)

Examples:

CREATE TABLE emp (
employee_id INTEGER
CONSTRAINT am_employees_pk PRIMARY KEY,
manager_id INTEGER,
first_name VARCHAR2(10) NOT NULL,
last_name VARCHAR2(10) NOT NULL,
title VARCHAR2(20),
salary NUMBER(6, 0)
);

INSERT INTO emp (
employee_id, manager_id, first_name, last_name, title, salary
) VALUES (
1, NULL, 'James', 'Smith', 'CEO', 800000
);

INSERT INTO emp (
employee_id, manager_id, first_name, last_name, title, salary
) VALUES (
2, 1, 'Ron', 'Johnson', 'Sales Manager', 600000
);

INSERT INTO emp (
employee_id, manager_id, first_name, last_name, title, salary
) VALUES (
3, 2, 'Fred', 'Hobbs', 'Salesperson', 150000
);

INSERT INTO emp (
employee_id, manager_id, first_name, last_name, title, salary
) VALUES (
4, 2, 'Susan', 'Jones', 'Salesperson', 500000
);
-- commit the transaction
COMMIT;

• Simple query with ROW_NUMBER function.

• Simple query with FIRST_VALUE function

The above query shows the ‘Highest Salary’ of employees who are managed by the same manager.

• Simple query with the LAST_VALUE function

The above query shows the ‘Lowest Salary’ of employees who are managed by the same manager.