Definition:
The Oracle PL/SQL function LAST is used to return the last value from an ordered sequence. It can be used for both aggregate and analytic action on a group of logically sorted rows. It operates on values of these rows that are eligible to be ranked first or last. The rows can be grouped using any of the analytic functions like MIN, MAX, SUM, AVG, COUNT, VARIANCE or STDDEV.
Example Syntax:
[AGGREGATE FUNCTION] KEEP (DENSE_RANK LAST ORDER BY [COLUMN | EXPRESSION] [ASC | DESC] )
OVER (PARTITION BY [COLUMN] )
In the syntax, KEEP keyword is used as an indication that only LAST values are returned. Note that DENSE_RANK is ranks the grouped rows and used to extract minimum or maximum value.
Example Usage:
SELECT
MAX(city) KEEP (DENSE_RANK FIRST ORDER_BY SUM(temperature))
AS highest_temp,
MIN(city) KEEP (DENSE_RANK LAST ORDER_BY SUM(temperature))
AS lowest_temp
FROM temperature_log
GROUP_BY city
ORDER_BY city;
Related Links:
Related Code Snippets:
- Last_Day function - The last_day function returns the last day of the month based on a date value.
