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.
Pagination is the process of dividing query results (typically on a user interface) into discrete pages, where each page contains a smaller/more manageable number of rows. In classic offset pagination, each page shows a fixed count of rows (say N): if N is 20, then the first page shows 1-20 rows, a “Next” navigation moves control to the second page with 21-40 rows, and so on.
This article demonstrates how to write queries to achieve this style of pagination in Oracle.
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.
A common scenario: in the development environment, a table’s ID column is missing its unique 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 the DBA escape this catch-22? For sure, says this post — with an approach of replacing *only* the duplicate IDs with unique values, and letting everything else stay untouched.
LEAD/LAG are analytic functions that provide access to more than one row of a table at the same time, without a self join. Let’s see how.
Take a table that stores the master list of exam grades, mapped to the upper limit up to which the grade applies. The table (GRADE_MASTER) has two columns: {GRADE_CODE, SCORE_UPTO}. For exam scores in the range 0-100, GRADE_MASTER specifies the A-F.
SQL> desc grade_master Name Null? Type ----------------- -------- ----------- GRADE_CODE NOT NULL VARCHAR2(2) SCORE_UPTO NOT NULL NUMBER(3) SQL> select * from grade_master; GR SCORE_UPTO -- ---------- F 59 D 69 C 79 B 89 A 100
The above data means that grade F applies to scores 0-59, D applies to scores 60-69, C to scores 70-79, and so on.
To find the grade for a given the examination score, the SQL needs to compare the EXTENT values across *two* rows. Comparing a column’s value across more than one row can be tricky to implement – unless you turn to Oracle functions LEAD/LAG.
In Oracle SQL, we often need to select rows with max 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 query can be extended to select rows based on the maximum value of any other column.
“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 to search the archives.
Here comes 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.