The ROW_NUMBER clause in Oracle SQL / PLSQL is basically a windowing clause and is used to assign a unique row number to fetched records based on an ordered list. ROW_NUMBER clause starts numbering from 1. ROW_NUMBER clause can be used with and without PARTITION BY clause. Syntax for using the ROW_NUMBER clause without PARTITION […]
clause
UNBOUNDED PRECEDING Clause with PARTITION BY in Oracle SQL – PLSQL
The UNBOUNDED PRECEDING is a windowing clause which defines the aggregation window i.e. the extent of rows to be used in aggregation. It tells oracle, the extent from where the rows are to be aggregated in the subgroup. Syntax for the UNBOUNDED PRECEDING clause with PARTITION BY in Oracle SQL / PLSQL is: SELECT columns […]
UNBOUNDED PRECEDING Clause without PARTITION BY in Oracle SQL – PLSQL
The UNBOUNDED PRECEDING is a windowing clause which defines the aggregation window i.e. the extent of rows to be used in aggregation. It tells oracle, the extent from where the rows are to be aggregated in the subgroup. Syntax for the UNBOUNDED PRECEDING clause without PARTITION BY in Oracle SQL / PLSQL is: SELECT columns […]
ROLLUP Clause in Oracle SQL – PLSQL
In simple terms the ROLLUP clause is used to get the subtotal and grand total in a set of fetched records based on groups. In other words we can say that ROLLUP clause extends the functionality of the GROUP BY Clause by returning rows containing a subtotal for each group along with a grand total […]
OVER Clause in Oracle SQL – PLSQL
In simple terms the OVER clause in Oracle SQL / PLSQL specifies the partition or order in which an analytical function will operate. Syntax for the OVER clause in Oracle SQL / PLSQL is: SELECT columns ,aggregate_function OVER (PARTITION BY column(s)) FROM table_name; Example 1: Using OVER clause Suppose we have a table named ‘employee’ […]