Forum Stats

  • 3,734,025 Users
  • 2,246,859 Discussions
  • 7,856,998 Comments

Discussions

analytical functions?(maybe)

alinux
alinux Member Posts: 232 Blue Ribbon
edited July 2008 in SQL & PL/SQL
Hi all,

I have a table like this
contract         amount        seq_id
------------------------------------------------------
1                      6                1            
1                      -70             2
2                      12             3
1                      -40             4
2                      -1               5
5                      8                6
6                      3                7
1                      6                8

create table a (contract number, amount number,seq_id number);
insert into a values (1,6,1);
insert into a values (1,-70,2);
insert into a values (2,12,3);
insert into a values (1,-40,4);
insert into a values (2,-1,5);
insert into a values (5,8,6);
insert into a values (6,3,7);
insert into a values (1,6,8);
what is the best solution to select all the contracts with their seq_id that have for the last seq_id (max (seq_id)) a negative amount.

in my example I will only select the contract 2 with seq_id = 5 because on his last seq_id it have a negative amount.

Can this be done doing only one query on my table, without using a correlated query?

Thanks

Comments

  • Nicolas Gasparotto
    Nicolas Gasparotto Member Posts: 25,514 Silver Crown
    A try :
    SQL> select contract, min(amount) keep (dense_rank last order by seq_id) amount, max(seq_id)
    2 from (select contract, amount, seq_id, last_value(amount) over (partition by contract order by seq_id rows between unbounded preceding and unbounded following) lv from tbl_a)
    3 where lv < 0
    4 group by contract;

    CONTRACT AMOUNT MAX(SEQ_ID)
    ---------- ---------- -----------
    2 -1 5

    SQL>
    Nicolas.
  • Rob van Wijk
    Rob van Wijk Member Posts: 5,856 Bronze Trophy
    Another try:
    SQL> set autotrace on
    SQL> select contract
    2 , min(amount) keep (dense_rank last order by seq_id) amount
    3 , max(seq_id)
    4 from ( select contract
    5 , amount
    6 , seq_id
    7 , last_value(amount) over (partition by contract order by seq_id rows between unbounded preceding and unbounded foll
    owing) lv
    8 from a
    9 )
    10 where lv < 0
    11 group by contract
    12 /

    CONTRACT AMOUNT MAX(SEQ_ID)
    ---------- ---------- -----------
    2 -1 5

    1 row selected.


    Execution Plan
    ----------------------------------------------------------
    Plan hash value: 3035105881

    -----------------------------------------------------------------------------
    | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
    -----------------------------------------------------------------------------
    | 0 | SELECT STATEMENT | | 8 | 416 | 5 (20)| 00:00:01 |
    | 1 | SORT GROUP BY NOSORT| | 8 | 416 | 5 (20)| 00:00:01 |
    |* 2 | VIEW | | 8 | 416 | 5 (20)| 00:00:01 |
    | 3 | WINDOW SORT | | 8 | 312 | 5 (20)| 00:00:01 |
    | 4 | TABLE ACCESS FULL| A | 8 | 312 | 4 (0)| 00:00:01 |
    -----------------------------------------------------------------------------

    Predicate Information (identified by operation id):
    ---------------------------------------------------

    2 - filter("LV"<0)

    Note
    -----
    - dynamic sampling used for this statement


    Statistics
    ----------------------------------------------------------
    68 recursive calls
    0 db block gets
    17 consistent gets
    0 physical reads
    0 redo size
    294 bytes sent via SQL*Net to client
    239 bytes received via SQL*Net from client
    2 SQL*Net roundtrips to/from client
    1 sorts (memory)
    0 sorts (disk)
    1 rows processed

    SQL> select contract
    2 , max(amount) keep (dense_rank last order by seq_id) amount
    3 , max(seq_id)
    4 from a
    5 group by contract
    6 having max(amount) keep (dense_rank last order by seq_id) < 0
    7 /

    CONTRACT AMOUNT MAX(SEQ_ID)
    ---------- ---------- -----------
    2 -1 5

    1 row selected.


    Execution Plan
    ----------------------------------------------------------
    Plan hash value: 3059106371

    ----------------------------------------------------------------------------
    | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
    ----------------------------------------------------------------------------
    | 0 | SELECT STATEMENT | | 8 | 312 | 5 (20)| 00:00:01 |
    |* 1 | FILTER | | | | | |
    | 2 | SORT GROUP BY | | 8 | 312 | 5 (20)| 00:00:01 |
    | 3 | TABLE ACCESS FULL| A | 8 | 312 | 4 (0)| 00:00:01 |
    ----------------------------------------------------------------------------

    Predicate Information (identified by operation id):
    ---------------------------------------------------

    1 - filter(MAX("AMOUNT") KEEP (DENSE_RANK FIRST ORDER BY
    INTERNAL_FUNCTION("SEQ_ID") DESC )<0)

    Note
    -----
    - dynamic sampling used for this statement


    Statistics
    ----------------------------------------------------------
    4 recursive calls
    0 db block gets
    11 consistent gets
    0 physical reads
    0 redo size
    295 bytes sent via SQL*Net to client
    239 bytes received via SQL*Net from client
    2 SQL*Net roundtrips to/from client
    1 sorts (memory)
    0 sorts (disk)
    1 rows processed
    Regards,
    Rob.
  • 561093
    561093 Member Posts: 2,146
    Like this?
    SQL> select * from (
    2 select contract, amount, seq_id,
    3 last_value(amount) over (partition by contract
    4 order by seq_id
    5 rows between unbounded preceding
    6 and unbounded following) last_amt
    7 from a)
    8 where last_amt < 0 and last_amt = amount;

    CONTRACT AMOUNT SEQ_ID LAST_AMT
    ---------- ---------- ---------- ----------
    2 -1 5 -1

    SQL> insert into a values (1, -9, 9);

    1 row created.

    SQL> select * from (
    2 select contract, amount, seq_id,
    3 last_value(amount) over (partition by contract
    4 order by seq_id
    5 rows between unbounded preceding
    6 and unbounded following) last_amt
    7 from a)
    8 where last_amt < 0 and last_amt = amount;

    CONTRACT AMOUNT SEQ_ID LAST_AMT
    ---------- ---------- ---------- ----------
    1 -9 9 -9
    2 -1 5 -1

    SQL> insert into a values (1, 2, 10);

    1 row created.

    SQL> select * from (
    2 select contract, amount, seq_id,
    3 last_value(amount) over (partition by contract
    4 order by seq_id
    5 rows between unbounded preceding
    6 and unbounded following) last_amt
    7 from a)
    8 where last_amt < 0 and last_amt = amount;

    CONTRACT AMOUNT SEQ_ID LAST_AMT
    ---------- ---------- ---------- ----------
    2 -1 5 -1

    SQL>
  • alinux
    alinux Member Posts: 232 Blue Ribbon
    :) very nice work....thanks for your time guys.
  • alinux
    alinux Member Posts: 232 Blue Ribbon
    edited June 2008
    that :keep (dense_rank last order by seq_id)

    What it is used for?

    it will not work without it?

    Thanks

    Message was edited by:
    alinux
  • Nicolas Gasparotto
    Nicolas Gasparotto Member Posts: 25,514 Silver Crown
    it will not work without it?
    Without that, no, that won't show you the amount corresponding to the amount for the max seq_id.

    Nicolas.
  • Nicolas Gasparotto
    Nicolas Gasparotto Member Posts: 25,514 Silver Crown
    having max(amount) keep (dense_rank last order by seq_id) < 0
    Nice one Rob.

    Nicolas.
  • alinux
    alinux Member Posts: 232 Blue Ribbon
    it will not work without it?
    Without that, no, that won't show you the amount corresponding to the amount >>for the max seq_id.
    Nicolas.
    You mean that without that it will take the entire 'minimum' and when using keep (dens_rank...) with will take the amount value for my last seq_id?

    I understood that: 'keep with dense_rank' is used with agregate/analitical...but what
    sense does 'min' have in : min(amount) if last is the last one value??

    sorry if my question is stupid
    Thanks in advance
  • Nicolas Gasparotto
    Nicolas Gasparotto Member Posts: 25,514 Silver Crown
    You mean that without that it will take the entire 'minimum' and when using keep (dens_rank...) with will take the amount value for my last seq_id?
    Yes.
    but what sense does 'min' have in : min(amount) if last is the last one value??
    The order is by an other column, seq_id. We said to keep the last value ordered by seq_id ascending, and in case there is more than one seq_id with the same value, then we will take the minimum of amount among those same seq_id.

    Nicolas.
  • alinux
    alinux Member Posts: 232 Blue Ribbon
    Without keep dens_rank it will get the minimum for all seq_id's for one contract.
    when using keep we will take the amount coresponding to the last seq_id when ordered ascending. (take only seq_ids that have negative coresponing amounts).

    seq_id is a sequance.
  • 575729
    575729 Member Posts: 412
    although its an old thread by i was looking at the following query written by Rob.

    Select contract, max (amount) keep (dense_rank last order by seq_id) amount, max (seq_id)
    from a
    group by contract
    having max (amount) keep (dense_rank last order by seq_id) < 0 /

    here he has used analytic in having by clause..while oracle doc says analtics can only be used in select and order by clause

    Question:
    which other analytic functions can be used in the having clause
  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 40,497 Red Diamond
    Hi,

    In your example:
    having max (amount) keep (dense_rank last order by seq_id) < 0 /
    max is an aggregate function.

    Almost all of the aggregate functions have analytic counterparts. You can tell them apart because the analytic versions always have "OVER (analytic_clause)" after the argument list.
This discussion has been closed.