syntax and example of LAST function in oracle sql

The LAST Function in Oracle SQL / PLSQL is used to get the last value in an ordered set of records.

The LAST function can be used with the following functions.

1. MIN()
2. MAX()
3. COUNT()
4. SUM()
5. AVG()
6. STDDEV() and
7. VARIANCE()

Syntax for using the LAST function in Oracle SQL / PLSQL is ;
SELECT
AGGREGATE_FUNCTION() KEEP (RANK_FUNCTION LAST ORDER BY AGGREGATE_FUNCTION(COLUMN))
FROM table_name
GROUP BY column(s);

Suppose we have a table named ‘employee’ as shown below:

Employee_ID	Employee_Name	Salary	Department	Commission
101	Emp A	10000	Sales	10
102	Emp B	20000	IT	20
103	Emp C	28000	IT	20
104	Emp D	30000	Support
105	Emp E	32000	Sales	10
106	Emp F	40000	Sales	10
107	Emp G	12000	Sales	10
108	Emp H	12000	Sales	10

Now, suppose we want to get the name of the department where the total salary being given is higher than total of salary in any other department, then we can achieve the same using LAST function as:

SELECT MAX(department) KEEP (DENSE_RANK LAST ORDER BY SUM(salary)) Max_Sal_Department
FROM employee
GROUP BY department;

We will get the following result:

MAX_SAL_DEPARTMENT

Sales

Here we can see that we have successfully fetched the name of the department in which the total salary is greater than total salary of any other department.