Oracle 12c Analytic Function

Question

Is there a way to obtain the corresponding value X for a minimum value Y in a given dataset, in the same record, using Oracle Analytic functions, and without using a subquery?

For example:

If I have the following dataset "ds1":

Col1  Col2
A     1
B     2
C     3
D     4
E     4
A     10

Normally, in order to find the value "A" in Col1, which corresponds to the minimum value "1" in Col2, I would write the following query:

select ds1.col1
     from ds1
          , (select min (col2) col2
                  from ds1) min_ds1
     where ds1.col2 = min_ds1.col2
/

Here is the executed code for such a Test Case:

### 1014.010, Start time is: 10/30/2019 11:39:35am

MYUN@MYDB-C1>>create table ds1 (col1 varchar2 (1), col2 number)
  2  /

Table created.
Elapsed: 00:00:00.01

MYUN@MYDB-C1>>insert into ds1 (col1, col2)
  2       select 'A', 1 from dual
  3       union all select 'B', 2 from dual
  4       union all select 'C', 3 from dual
  5       union all select 'D', 4 from dual
  6       union all select 'E', 4 from dual
  7       union all select 'A', 10 from dual
  8  /

6 rows created.
Elapsed: 00:00:00.02

MYUN@MYDB-C1>>commit
  2  /

Commit complete.
Elapsed: 00:00:00.01

MYUN@MYDB-C1>>col col1 format a10
MYUN@MYDB-C1>>select ds1.col1
  2       from ds1
  3            , (select min (col2) col2
  4                    from ds1) min_ds1
  5       where ds1.col2 = min_ds1.col2
  6  /

COL1
----------
A

1 row selected.
Elapsed: 00:00:00.01

MYUN@MYDB-C1>>drop table ds1
  2  /

Table dropped.
Elapsed: 00:00:00.03
The time now: 10/30/2019 11:39:36am

My question is:

Is it possible to derive the value "A" using an Analytic Function and without requiring a subquery? I am aware I can use the analytic function "ROW_NUMBER", sort the result in the ORDER BY clause, all in a subquery and then add a WHERE clause on the outer query where I say something like "WHERE RN = 1", where "RN" is the alias for the column in the subquery where the ROW_NUMBER function is used.

Why do you have the constraint "without requiring a subquery"? Would a better requirement to be using only a single table (or index) scan as you can still use sub-queries solely for filtering but this eliminates self-joins and correlated sub-queries. — MT0, Oct 30 '19 at 16:06
The constraint is for code simplicity. The query is already several thousand characters long, say around 4000. I need to add 10 subqueries to the query, even if the subqueries don't have subqueries themselves. So if the new subqueries require subqueries it will make the code exponentially more complex as opposed to if the new subqueries are simple "SELECT" statements. — M. Kemp, Oct 30 '19 at 16:16
Actually it's about 15k characters to begin with, before adding the new stuff. — M. Kemp, Oct 30 '19 at 16:19

MT0 · Answer 1 · 2019-10-30T16:33:21.680

5

Use an aggregation function with KEEP to get the minimum values for another column:

Oracle Setup:

create table ds1 ( col1, col2 ) AS
  select 'A', 1 from dual
  union all select 'B', 2 from dual
  union all select 'C', 3 from dual
  union all select 'D', 4 from dual
  union all select 'E', 4 from dual
  union all select 'F', 10 from dual;

Aggregation Query:

SELECT MIN( col1 ) KEEP ( DENSE_RANK FIRST ORDER BY col2 ) AS col1
FROM   ds1

Output:

| COL1 |
| :--- |
| A    |

Analytic Query:

If you particularly want an analytic function then:

SELECT col1, col2
FROM   (
  SELECT ds1.*,
         DENSE_RANK() OVER ( ORDER BY col2 ASC ) AS rnk
  FROM   ds1
)
WHERE  rnk = 1

This has a sub-query but there is only a single table-scan.

You can easily integrate it into a huge query:

WITH my_huge_query AS (
  <paste your huge query here>
)
SELECT *
FROM   (
  SELECT m.*,
         DENSE_RANK() OVER( ORDER BY col2 ASC ) AS rnk
  FROM   my_huge_query m
)
WHERE  rnk = 1

Output:

COL1 | COL2
:--- | ---:
A    |    1

db<>fiddle here

edited Oct 30 '19 at 16:33

answered Oct 30 '19 at 15:54

MT0

143,790
11
59
117

Dear God, WHAT is that?!! And it actually works!! I'm talking about the Aggregation Query option. As Dave Chappelle would say, I wish I had four hands so I could give you four thumbs up!! Thank you. I found this blog on the subject, but I'm not reading it all unless I have to. Too intense!! https://rwijk.blogspot.com/2012/09/keep-clause.html Pretty interesting. I'll apply this to the actual Use Case and report back here if it works in the wild. I have every expectation that it will but there's nothing quite like real-world tests, expectations being what they are. Thanks again. – M. Kemp Oct 30 '19 at 16:09
1

@M.Kemp Read the aggregation function from right-to-left. First `ORDER BY col2 ASC` then only `KEEP` those rows which were `RANK`ed `FIRST` in that previous ordering and then find the `MIN`imum value for `col1` from those kept rows. – MT0 Oct 30 '19 at 16:15
Works in real-world. #stunned #amazed #gratitude – M. Kemp Oct 30 '19 at 16:17

Oracle 12c Analytic Function

1 Answers1