Course Content
What is Oracle
0/1
Oracle Tables
0/1
CREATE TABLE AS in Oracle
0/1
ALTER TABLE In Oracle
0/1
DROP TABLE In Oracle
0/1
TRUNCATE TABLE In Oracle
0/1
QUERIES In Oracle
0/1
INSERT Query In Oracle
0/1
SELECT Query In Oracle
0/1
UPDATE Query In Oracle
0/1
DELETE Query In Oracle
0/1
INSERT ALL In Oracle
0/1
GROUP BY Clause In Oracle
0/1
HAVING Clause In Oracle
0/1
ORDER BY Clause In Oracle
0/1
MINUS In Oracle
0/1
INTERSECT Operator In Oracle
0/1
TRIGGER In Oracle
0/1
AFTER TRIGGER In Oracle
0/1
BEFORE TRIGGER In Oracle
0/1
ENABLE TRIGGER In Oracle
0/1
DISABLE TRIGGER In Oracle
0/1
DROP TRIGGER In Oracle
0/1
CURSOR In Oracle
0/1
VIEW In Oracle
0/1
DISTINCT Clause In Oracle
0/1
PROCEDURE In Oracle
0/1
UNION In Oracle
0/1
UNION ALL In Oracle
0/1
OUTER JOIN In Oracle
0/1
INNER JOIN In Oracle
0/1
CROSS JOIN In Oracle
0/1
EQUI JOIN In Oracle
0/1
SEMI JOIN In Oracle
0/1
SELF JOIN In Oracle
0/1
ANTI JOIN In Oracle
0/1
ROWNUM In Oracle
0/1
SQLERRM Function In Oracle
0/1
SQLCODE Function In Oracle
0/1
USERENV Function In Oracle
0/1
ACOS Function In Oracle
0/1
ADD_MONTHS Function In Oracle
0/1
ASCII Function In Oracle
0/1
ASCIISTR Function In Oracle
0/1
ASIN Function In Oracle
0/1
ATAN Function In Oracle
0/1
ATAN2 Function In Oracle
0/1
AVG Function In Oracle
0/1
BFILENAME Function In Oracle
0/1
ABS Function In Oracle
0/1
BIN_TO_NUM Function In Oracle
0/1
BITAND Function In Oracle
0/1
CARDINALITY Function In Oracle
0/1
CASE Function In Oracle
0/1
CAST Function In Oracle
0/1
CEIL Function In Oracle
0/1
CHARTOROWID Function In Oracle
0/1
CHR Function In Oracle
0/1
COALESCE Function In Oracle
0/1
COMPOSE Function In Oracle
0/1
CONCAT With Function In Oracle
0/1
CONCAT Function In Oracle
0/1
CONVERT Function In Oracle
0/1
CORR Function In Oracle
0/1
COS Function In Oracle
0/1
COSH Function In Oracle
0/1
COUNT Function In Oracle
0/1
COVAR_POP Function In Oracle
0/1
COVAR_SAMP Function In Oracle
0/1
CUME_DIST Function In Oracle
0/1
CURRENT_DATE In Oracle
0/1
CURRENT_TIMESTAMP Function In Oracle
0/1
DBTIMEZONE Function In Oracle
0/1
DECODE Function In Oracle
0/1
DECOMPOSE Function In Oracle
0/1
DENSE_RANK Function In Oracle
0/1
DUMP Function In Oracle
0/1
EMPTY_BLOB Fuction In Oracle
0/1
EMPTY_CLOB Function In Oracle
0/1
EXP Function In Oracle
0/1
EXTRACT Function In Oracle
0/1
FIRST_VALUE Function In Oracle
0/1
FLOOR Function In Oracle
0/1
FROM_TZ Function In Oracle
0/1
GLOBAL TEMP TABLES In Oracle
0/1
GREATEST Function In Oracle
0/1
GROUP_ID Function In Oracle
0/1
HEXTORAW Function In Oracle
0/1
INITCAP Function In Oracle
0/1
INSTR Function In Oracle
0/1
INSTR2 Function In Oracle
0/1
VARIANCE Function In Oracle
0/1
VAR_SAMP Function In Oracle
0/1
VAR_POP Function In Oracle
0/1
USER Function In Oracle
0/1
UID Function In Oracle
0/1
TZ_OFFSET Function In Oracle
0/1
TRUNC (Numbers) Function In Oracle
0/1
TRUNC (Dates) Function In Oracle
0/1
TO_YMINTERVAL Function In Oracle
0/1
TO_TIMESTAMP_TZ Function In Oracle
0/1
TO_TIMESTAMP Function In Oracle
0/1
TO_SINGLE_BYTE Function In Oracle
0/1
TO_NUMBER Function In Oracle
0/1
TO_NCLOB Function In Oracle
0/1
TO_MULTI_BYTE Function In Oracle
0/1
TO_LOB Function In Oracle
0/1
TO_DSINTERVAL Function In Oracle
0/1
TO_DATE Function In Oracle
0/1
TO_CLOB Function In Oracle
0/1
TO_CHAR Function In Oracle
0/1
TANH Function In Oracle
0/1
TAN Function In Oracle
0/1
SYSTIMESTAMP Function In Oracle
0/1
SYSDATE Function In Oracle
0/1
SYS_CONTEXT Function In Oracle
0/1
SQRT Function In Oracle
0/1
SESSIONTIMEZONE Function In Oracle
0/1
LOG Function In Oracle
0/1
LAST_DAY Function In Oracle
0/1
LEAST Function In Oracle
0/1
LENGTH Function In Oracle
0/1
LENGTH2 Function In Oracle
0/1
INSTR4 Function In Oracle
0/1
INSTRB Function In Oracle
0/1
INSTRC Function In Oracle
0/1
LAG Function In Oracle
0/1
LAST_VALUE Function In Oracle
0/1
LEAD Function In Oracle
0/1
LENGTH4 Function In Oracle
0/1
LENGTHB Function In Oracle
0/1
LENGTHC Function In Oracle
0/1
LISTAGG Function In Oracle
0/1
LN Function In Oracle
0/1
LNNVL Function In Oracle
0/1
LOCAL TEMP TABLES In Oracle
0/1
LOCALTIMESTAMP Function In Oracle
0/1
MAX Function In Oracle
0/1
MEDIAN Function In Oracle
0/1
MIN Function In Oracle
0/1
MOD Function In Oracle
0/1
MONTHS_BETWEEN Function In Oracle
0/1
NANVL Function In Oracle
0/1
NEW_TIME Function In Oracle
0/1
NEXT_DAY Function In Oracle
0/1
NULLIF Function In Oracle
0/1
NUMTODSINTERVAL Function In Oracle
0/1
NUMTOYMINTERVAL Function In Oracle
0/1
NVL Function In Oracle
0/1
NVL2 Function In Oracle
0/1
POWER Function In Oracle
0/1
STDDEV Function In Oracle
0/1
NTH_VALUE Function In Oracle
0/1
SINH Function In Oracle
0/1
SIN Function In Oracle
0/1
SIGN Function In Oracle
0/1
ROUND (Numbers) Function In Oracle
0/1
FROM Clause In Oracle
0/1
FUNCTION In Oracle
0/1
SUM Function In Oracle
0/1
ROUND (Dates) Function In Oracle
0/1
REMAINDER Function In Oracle
0/1
JOINS In Oracle
0/1
RANK Function In Oracle
0/1
RAWTOHEX Function In Oracle
0/1
REGEXP_COUNT Function In Oracle
0/1
Oracle Tutorial
About Lesson

LAST_VALUE Function In Oracle

LAST_VALUE is one of the vital Analytic functions of Oracle. It is used to get the last value in an ordered set of values from an analytic window. The LAST_VALUE function is supported in the various versions of the Oracle/PLSQL, including, Oracle 12c, Oracle 11g, Oracle 10g and Oracle 9i.
Syntax 1:

LAST_VALUE (expression)
 [RESPECT NULLS | IGNORE NULLS]
 OVER ([query_partition_clause] [order_by_clause [windowing_clause]])

Syntax 2:

LAST_VALUE (expression
 [RESPECT NULLS | IGNORE NULLS])
 OVER ([query_partition_clause] [order_by_clause [windowing_clause]])

Parameters:
expression: It is used to specify the expressions or column whose last value needs to be retrieved.
RESPECT NULLS | IGNORE NULLS: It is an optional parameter which is used to specify whether to include or ignore the NULL values in the calculation. The default value is RESPECT NULLS.
query_partition_clause: It is also an optional parameter which is used to partition the results into groups.
order_by_clause: It is also an optional parameter which is used to order the data within each partition.
windowing_clause: It is also an optional parameter which is used to specify the rows in the analytic window to be evaluated.

Values of windowing_clause:

RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW: It is the default value that changes the Last row in the window with a change in the current row.
RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING : It changes the First row in the windows with a change in the current row.
RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING : It includes all the rows in the window, regardless of the current row.

Example 1: Without using query_partition_clause.
Students Table:

ID	MARKS	EXTRA_MARKS
1	80	10
2	100	20
3	95	15
4	75	10
5	85	15

Query:

SELECT DISTINCT LAST_VALUE (marks)
OVER (ORDER BY marks ASC
RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)
AS "HIGHEST"
FROM students;

Output:

HIGHEST
100

Explanation:
Here, the LAST_VALUE function is used to get the highest ‘marks’ from the ‘students’ table. The ORDER BY clause will allow the analytic window to sort the data in ascending order. The windowing clause will then include all the rows in the window, regardless of the current row.

Example 2: Using the query_partition_clause.
Students Table:

ID	MARKS	EXTRA_MARKS
1	80	10
2	100	20
3	95	15
4	75	10
5	85	15

Query:

SELECT DISTINCT extra_marks, LAST_VALUE (marks)
 OVER (PARTITION BY extra_marks ORDER BY marks ASC
       RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)
       AS "HIGHEST"
FROM students
WHERE extra_marks in (10, 15)
ORDER BY extra_marks;

Output:

EXTRA_MARKS	HIGHEST
10	        80
15	        95

Explanation:
Here, the LAST_VALUE function is used to get the highest ‘marks’ from the ‘students’ table. The ORDER BY clause will allow the analytic window to sort the data in ascending order after the partition of the results by ‘extra_marks’. The windowing clause will then include all the rows in the window, regardless of the current row.

error: Content is protected !!