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 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 →
LEAD LAG Analytic Functions

Using LEAD/LAG to Place a Value within Extents

Take a table with the structure {<Category>, <Extent>}– say, a GRADE_MASTER table with two columns: {GRADE_CODE, SCORE_UPTO}. For examination scores in the range 0-100, GRADE_MASTER specifies the extent of score up to which a certain grade applies. To find the grade for a given the examination score, the SQL needs to compare the EXTENT values […]

Read the Full Article →
Select Rows With Max Value

Select Rows With Max Value

We often need to select rows based on the maximum value of a specific column, such as date. Let’s say we have a table that stores customers’ orders, and we want to list the last order dates and amounts for each customer. Here is a demo data set and query to achieve this. The same […]

Read the Full Article →
Top N Query in Oracle

Nth Highest Salary in Oracle

“How can I select the Nth highest salary of the EMP table?” This is a question that every Oracle newbie stumbles over. Ask it on a forum and you’re pointed to the archives. That gets you nowhere as when you search the archives, all you find is a host of other messages also asking you […]

Read the Full Article →