Analytic Functions

Oracle analytic functions are extensions to SQL for computing aggregates based on a group of rows. Analytic functions can appear in the SELECT or ORDER BY clause, with an optional windowing clause.

Thumbnail image for Pagination Queries in Oracle: Analytics, Row Limiting Clause

Pagination Queries in Oracle: Analytics, Row Limiting Clause

How to implement pagination queries in Oracle, using (1) analytic functions (2) Oracle’s row limiting clause to fetch N rows from a given offset.

Read the Full Article →
Thumbnail image for SQL to Select Rows Conditionally Based On Column Value

SQL to Select Rows Conditionally Based On Column Value

SQL solution to select rows conditionally based on column value – for example, a priority column tagged to a set of rows, with a requirement to select only the rows with top priority.

Read the Full Article →
Thumbnail image for Replace Duplicates in ID Column with Unique Values

Replace Duplicates in ID Column with Unique Values

A solution to the requirement of replacing only the duplicate IDs with unique values in a database table, and letting everything else stay untouched.

Read the Full Article →
Thumbnail image for LEAD/LAG Functions to Place a Value within Extents

LEAD/LAG Functions to Place a Value within Extents

Analytic functions LEAD and LAG provide access to more than one row of a table at the same time, without a self join. This feature can be used to find the position of a value within extents, where the extents are defined by the upper bound only and the lower bound is derived via comparison to the previous/next row.

Read the Full Article →
Thumbnail image for Select Rows With Max Value

Select Rows With Max Value

Solution for selecting rows based on the maximum value of a specific table column, such as date. See it in action: script to list the latest order details of every customer’s orders.

Read the Full Article →
Thumbnail image for Nth Highest Salary in Oracle

Nth Highest Salary in Oracle

“How can I select the Nth highest salary of the EMP table?” The answer to the problem of finding the Nth highest salary. You can extend this logic to find the Nth highest row of any table.

Read the Full Article →