analytics

SQL to Select Rows Conditionally Based On Column Value

SQL to Select Rows Conditionally Based On Column Value

Let’s say a table contains multiple rows for an id. The requirement is to select only one of those rows, based on the value in a "type" column which determines the row’s priority. A typical example is selecting one contact number for a customer, based on contact types.

Read the Full Article →
Replace Duplicate Ids with Unique Values

Replace Duplicates in ID Column with Unique Values

A common scenario: in the development environment, a table’s ID column is missing its unique key constraint, and developers have been creating their own data for testing. Suddenly someone realizes that the ID column has duplicates. The DBA is summoned. "Enable the unique key", the DBA is told, "but do not delete our data." Can […]

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

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 →
Thumbnail image for 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 →