Hi Tom,
Is there any performance gain by using mod(:cash,c1) rather than :cash-(trunc(:cash/c1)*c1)?

select :cash,
decode( c1, 0, null, c1 || ' ' || txtc1 ) || ' ' ||
decode( c2, 0, null, c2 || ' ' || txtc2 ) || ' ' ||
decode( c3, 0, null, c3 || ' ' || txtc3 ) || ' ' ||
decode( c4, 0, null, c4 || ' ' || txtc4 ) || ' ' ||
decode( c5, 0, null, c5 || ' ' || txtc5 ) || ' ' ||
decode( trunc(rcash/c6), 0, null, trunc(rcash/c6) || ' ' || txtc6 )
from (
select :cash, c1, c2, c3, c4, trunc(rcash/c5) c5, mod(rcash,c5)
rcash, c6, txtc1, txtc2, txtc3, txtc4,
txtc5, txtc6
from (
select :cash, c1, c2, c3, trunc(rcash/c4) c4, mod(rcash,c4) rcash,
c5, c6, txtc1, txtc2, txtc3, txtc4,
txtc5, txtc6
from (
select :cash, c1, c2, trunc(rcash/c3) c3, mod(rcash,c3) rcash, c4,
c5, c6, txtc1, txtc2, txtc3, txtc4,
txtc5, txtc6
from (
select :cash, c1, trunc(rcash/c2) c2, mod(rcash,c2) rcash, c3, c4,
c5, c6, txtc1, txtc2, txtc3, txtc4,
txtc5, txtc6
from (
select :cash, trunc(:cash/c1) c1, mod(:cash,c1) rcash, c2, c3,
c4, c5, c6, txtc1, txtc2, txtc3, txtc4,
txtc5, txtc6
from (
select max(decode(rn,1,amt)) c1,
max(decode(rn,2,amt)) c2,
max(decode(rn,3,amt)) c3,
max(decode(rn,4,amt)) c4,
max(decode(rn,5,amt)) c5,
max(decode(rn,6,amt)) c6,
max(decode(rn,1,denomination)) txtc1,
max(decode(rn,2,denomination)) txtc2,
max(decode(rn,3,denomination)) txtc3,
max(decode(rn,4,denomination)) txtc4,
max(decode(rn,5,denomination)) txtc5,
max(decode(rn,6,denomination)) txtc6
from (
select amt, denomination, row_number() over (order by amt desc) rn
from money
)
)
)
)
)
)
)



Thanks in advance.
Regards,
Sajid Anwar
London.

select :cash, ltrim(decode(c1, 0, Null, c1 || ' ' || txtc1 || ' ') ||
decode(c2, 0, Null, c2 || ' ' || txtc2 || ' ') ||
decode(c3, 0, Null, c3 || ' ' || txtc3 || ' ') ||
decode(c4, 0, Null, c4 || ' ' || txtc4 || ' ') ||
decode(c5, 0, Null, c5 || ' ' || txtc5 || ' ') ||
decode(c6, 0, Null, c6 || ' ' || txtc6 || ' ') ||
decode(c7, 0, Null, c7 || ' ' || txtc7 || ' ') ||
decode(c8, 0, Null, c8 || ' ' || txtc8 || ' ') ||
decode(c9, 0, Null, c9 || ' ' || txtc9 || ' ') ||
decode(c10, 0, Null, c10 || ' ' || txtc10 || ' ') ||
decode(c11, 0, Null, c11 || ' ' || txtc11 || ' ') ||
decode(c12, 0, Null, c12 || ' ' || txtc12))
from (select :cash sal,
trunc(:cash/c1) c1, txtc1,
trunc(mod(:cash,c1) / c2) c2, txtc2,
trunc(mod(mod(:cash,c1),c2) / c3) c3, txtc3,
trunc(mod(mod(mod(:cash,c1),c2),c3) / c4) c4, txtc4,
trunc(mod(mod(mod(mod(:cash,c1),c2),c3),c4) / c5) c5, txtc5,
trunc(mod(mod(mod(mod(mod(:cash,c1),c2),c3),c4),c5) / c6) c6, txtc6,
trunc(mod(mod(mod(mod(mod(mod(:cash,c1),c2),c3),c4),c5),c6) / c7) c7, txtc7,
trunc(mod(mod(mod(mod(mod(mod(mod(:cash,c1),c2),c3),c4),c5),c6),c7) / c8) c8, txtc8,
trunc(mod(mod(mod(mod(mod(mod(mod(mod(:cash,c1),c2),c3),c4),c5),c6),c7),c8) / c9) c9, txtc9,
trunc(mod(mod(mod(mod(mod(mod(mod(mod(mod(:cash,c1),c2),c3),c4),c5),c6),c7),c8),c9) / c10) c10, txtc10,
trunc(mod(mod(mod(mod(mod(mod(mod(mod(mod(mod(:cash,c1),c2),c3),c4),c5),c6),c7),c8),c9),c10) / c11) c11, txtc11,
trunc(mod(mod(mod(mod(mod(mod(mod(mod(mod(mod(mod(:cash,c1),c2),c3),c4),c5),c6),c7),c8),c9),c10),c11) / c12) c12, txtc12
from (select max(decode(rn,1,amt)) c1, max(decode(rn,1,denomination)) txtc1,
max(decode(rn,2,amt)) c2, max(decode(rn,2,denomination)) txtc2,
max(decode(rn,3,amt)) c3, max(decode(rn,3,denomination)) txtc3,
max(decode(rn,4,amt)) c4, max(decode(rn,4,denomination)) txtc4,
max(decode(rn,5,amt)) c5, max(decode(rn,5,denomination)) txtc5,
max(decode(rn,6,amt)) c6, max(decode(rn,6,denomination)) txtc6,
max(decode(rn,7,amt)) c7, max(decode(rn,7,denomination)) txtc7,
max(decode(rn,8,amt)) c8, max(decode(rn,8,denomination)) txtc8,
max(decode(rn,9,amt)) c9, max(decode(rn,9,denomination)) txtc9,
max(decode(rn,10,amt)) c10, max(decode(rn,10,denomination)) txtc10,
max(decode(rn,11,amt)) c11, max(decode(rn,11,denomination)) txtc11,
max(decode(rn,12,amt)) c12, max(decode(rn,12,denomination)) txtc12
from (select amt, denomination, row_number() over (order by amt desc) rn
from money)))
/

I gave previous query for 12 Denominations. This is for 6 as example given in the first

select :cash, ltrim(decode(c1, 0, Null, c1 || ' ' || txtc1 || ' ') ||
decode(c2, 0, Null, c2 || ' ' || txtc2 || ' ') ||
decode(c3, 0, Null, c3 || ' ' || txtc3 || ' ') ||
decode(c4, 0, Null, c4 || ' ' || txtc4 || ' ') ||
decode(c5, 0, Null, c5 || ' ' || txtc5 || ' ') ||
decode(c6, 0, Null, c6 || ' ' || txtc6 || ' '))
from (select :cash sal,
trunc(:cash/c1) c1, txtc1,
trunc(mod(:cash,c1) / c2) c2, txtc2,
trunc(mod(mod(:cash,c1),c2) / c3) c3, txtc3,
trunc(mod(mod(mod(:cash,c1),c2),c3) / c4) c4, txtc4,
trunc(mod(mod(mod(mod(:cash,c1),c2),c3),c4) / c5) c5, txtc5,
trunc(mod(mod(mod(mod(mod(:cash,c1),c2),c3),c4),c5) / c6) c6, txtc6
from (select max(decode(rn,1,amt)) c1, max(decode(rn,1,denomination)) txtc1,
max(decode(rn,2,amt)) c2, max(decode(rn,2,denomination)) txtc2,
max(decode(rn,3,amt)) c3, max(decode(rn,3,denomination)) txtc3,
max(decode(rn,4,amt)) c4, max(decode(rn,4,denomination)) txtc4,
max(decode(rn,5,amt)) c5, max(decode(rn,5,denomination)) txtc5,
max(decode(rn,6,amt)) c6, max(decode(rn,6,denomination)) txtc6
from (select amt, denomination, row_number() over (order by amt desc) rn
from money)))

Hi Tom

I have a table called money where the data it contains is like this

SLNO AMT DENOMINATION
---------- ------- ------------
1 1000 1000's
2 500 500's
3 100 100's
4 50 50's
5 10 10's
6 5 5's

in the below sql query it is hardcoded it will always take only 6 columns. is there anymethod simpler than this and hardcoding is not there.

select :cash, ltrim(decode(c1, 0, Null, c1 || ' ' || txtc1 || ' ') ||
decode(c2, 0, Null, c2 || ' ' || txtc2 || ' ') ||
decode(c3, 0, Null, c3 || ' ' || txtc3 || ' ') ||
decode(c4, 0, Null, c4 || ' ' || txtc4 || ' ') ||
decode(c5, 0, Null, c5 || ' ' || txtc5 || ' ') ||
decode(c6, 0, Null, c6 || ' ' || txtc6 || ' '))
from (select :cash sal,
trunc(:cash/c1) c1, txtc1,
trunc(mod(:cash,c1) / c2) c2, txtc2,
trunc(mod(mod(:cash,c1),c2) / c3) c3, txtc3,
trunc(mod(mod(mod(:cash,c1),c2),c3) / c4) c4, txtc4,
trunc(mod(mod(mod(mod(:cash,c1),c2),c3),c4) / c5) c5, txtc5,
trunc(mod(mod(mod(mod(mod(:cash,c1),c2),c3),c4),c5) / c6) c6, txtc6
from (select max(decode(rn,1,amt)) c1, max(decode(rn,1,denomination)) txtc1,
max(decode(rn,2,amt)) c2, max(decode(rn,2,denomination)) txtc2,
max(decode(rn,3,amt)) c3, max(decode(rn,3,denomination)) txtc3,
max(decode(rn,4,amt)) c4, max(decode(rn,4,denomination)) txtc4,
max(decode(rn,5,amt)) c5, max(decode(rn,5,denomination)) txtc5,
max(decode(rn,6,amt)) c6, max(decode(rn,6,denomination)) txtc6
from (select amt, denomination, slno from money order by slno)))

Hi Tom

I have a table called money where the data it contains is like this

SLNO AMT DENOMINATION
---------- ------- ------------
1 1000 1000's
2 500 500's
3 100 100's
4 50 50's
5 10 10's
6 5 5's

output will look this this

cash new_col
---- -------
1600 1 1000's 1 500's 1 100's


in the below sql query it is hardcoded it will always take only 6 columns. is
there anymethod simpler than this and hardcoding is not there.

select :cash, ltrim(decode(c1, 0, Null, c1 || ' ' || txtc1 || ' ') ||
decode(c2, 0, Null, c2 || ' ' || txtc2 || ' ') ||
decode(c3, 0, Null, c3 || ' ' || txtc3 || ' ') ||
decode(c4, 0, Null, c4 || ' ' || txtc4 || ' ') ||
decode(c5, 0, Null, c5 || ' ' || txtc5 || ' ') ||
decode(c6, 0, Null, c6 || ' ' || txtc6 || ' ')) new_col
from (select :cash sal,
trunc(:cash/c1) c1, txtc1,
trunc(mod(:cash,c1) / c2) c2, txtc2,
trunc(mod(mod(:cash,c1),c2) / c3) c3, txtc3,
trunc(mod(mod(mod(:cash,c1),c2),c3) / c4) c4, txtc4,
trunc(mod(mod(mod(mod(:cash,c1),c2),c3),c4) / c5) c5, txtc5,
trunc(mod(mod(mod(mod(mod(:cash,c1),c2),c3),c4),c5) / c6) c6,
txtc6
from (select max(decode(rn,1,amt)) c1, max(decode(rn,1,denomination))
txtc1,
max(decode(rn,2,amt)) c2, max(decode(rn,2,denomination))
txtc2,
max(decode(rn,3,amt)) c3, max(decode(rn,3,denomination))
txtc3,
max(decode(rn,4,amt)) c4, max(decode(rn,4,denomination))
txtc4,
max(decode(rn,5,amt)) c5, max(decode(rn,5,denomination))
txtc5,
max(decode(rn,6,amt)) c6, max(decode(rn,6,denomination))
txtc6
from (select amt, denomination, slno from money order by
slno)))

Hello Tom, while I feel that a general solution can be found using analytic functions, I think that the MODEL clause is simpler in this case.
Here is a solution that apparently works with more than 6 rows in MONEY:

variable x number;

exec :x := 1286;

select
denomination, amt, result from (
select
denomination,
amt,
row_number() over (order by amt desc) rn
from money ) m
model
dimension by (rn)
measures ( cast(null as number) remainder,
cast(null as number) prev_remainder,
cast(null as number) result,
amt,
denomination)
RULES
(
remainder[rn] order by rn =
mod(
presentnnv( remainder[cv(rn) - 1], remainder[cv(rn) - 1], :x),
amt[cv(rn)]),
prev_remainder[rn] order by rn =
presentnnv( remainder[cv(rn) - 1], remainder[cv(rn) - 1], :x),
result[rn] = ( prev_remainder[cv(rn)] - remainder[cv(rn)] ) / amt[cv(rn)]
)
/

Of course this doesn't really answer the original question (which was version 9.2.0)...

Regards, Tony

By analytic function?  Yes, but not as neatly as (I think) the original questioner hoped.  *IF* analytic function could LAG or LEAD to the *results* of the previous analytic function on a row, *THEN* the query would be simple and recursive.  However, analytic functions are applied last, and so their results aren't so neatly accessible. Analytic function results have to be accessed in an outer query. 

Here's what I think the original questioner might have been considering, but I had to nest the queries to access the previous row results via LEAD.  The computations in each outer query are almost exact copies of each other, except for hardcoding the AMT of the monetary denomination.

variable cash number;
exec :cash := 118;

select amt
     , case when amt = 1 
            then lead("remainder") over (order by amt)
            when amt > 1 then "start" end               "start"
     , case when amt = 1 
            then trunc(lead("remainder") over (order by amt)/1)
            when amt > 1 then "# of bills" end          "# of bills"
     , case when amt = 1 
            then trunc(lead("remainder") over (order by amt)/1)*1
            when amt > 1 then "$ of bills" end          "$ of bills"
     , case when amt = 1 
            then lead("remainder") over (order by amt) - 
                 trunc(lead("remainder") over (order by amt)/1)*1
            when amt > 1 then "remainder" end           "remainder"
from
( -- $5  $5  $5   $5
select amt
     , case when amt = 5 
            then lead("remainder") over (order by amt)
            when amt > 5 then "start" end                 "start"
     , case when amt = 5 
            then trunc(lead("remainder") over (order by amt)/5)
            when amt > 5 then "# of bills" end           "# of bills"
     , case when amt = 5 
            then trunc(lead("remainder") over (order by amt)/5)*5
            when amt > 5 then "$ of bills" end           "$ of bills"
     , case when amt = 5 
            then lead("remainder") over (order by amt) - 
                 trunc(lead("remainder") over (order by amt)/5)*5
            when amt > 5 then "remainder" end            "remainder"
from
( -- $10  $10  $10   $10
select amt
     , case when amt = 10 
            then lead("remainder") over (order by amt)
            when amt > 10 then "start" end                   "start"
     , case when amt = 10 
            then trunc(lead("remainder") over (order by amt)/10)
            when amt > 10 then "# of bills" end          "# of bills"
     , case when amt = 10 
            then trunc(lead("remainder") over (order by amt)/10)*10
            when amt > 10 then "$ of bills" end          "$ of bills"
     , case when amt = 10 
            then lead("remainder") over (order by amt) - 
                 trunc(lead("remainder") over (order by amt)/10)*10
            when amt > 10 then "remainder" end            "remainder"
from
(  -- $20  $20  $20   $20
select amt
     , case when amt = 20 
            then lead("remainder") over (order by amt)
            when amt > 20 then "start" end                "start"
     , case when amt = 20 
            then trunc(lead("remainder") over (order by amt)/20)
            when amt > 20 then "# of bills" end           "# of bills"
     , case when amt = 20 
            then trunc(lead("remainder") over (order by amt)/20)*20
            when amt > 20 then "$ of bills" end           "$ of bills"
     , case when amt = 20 
            then lead("remainder") over (order by amt) - 
                 trunc(lead("remainder") over (order by amt)/20)*20
            when amt > 20 then "remainder" end            "remainder"
from
(  -- $50  $50  $50   $50
select amt
     , case when amt = 50 
            then lead("remainder") over (order by amt)
            when amt > 50 then "start" end                "start"
     , case when amt = 50 
            then trunc(lead("remainder") over (order by amt)/50)
            when amt > 50 then "# of bills" end          "# of bills"
     , case when amt = 50 
            then trunc(lead("remainder") over (order by amt)/50)*50
            when amt > 50 then "$ of bills" end          "$ of bills"
     , case when amt = 50 
            then lead("remainder") over (order by amt) - 
                 trunc(lead("remainder") over (order by amt)/50)*50
            when amt > 50 then "remainder" end            "remainder"
from
(
select amt
, decode(amt,100,:cash,0)                       "start"
, decode(amt,100,trunc(:cash/100),0)            "# of bills"
, decode(amt,100,trunc(:cash/100)*100,0)        "$ of bills"
, decode(amt,100,:cash-trunc(:cash/100)*100)    "remainder"
from money
)
)
)
)
) order by amt
/
SQL> /

       AMT      start # of bills $ of bills  remainder
---------- ---------- ---------- ---------- ----------
         1          3          3          3          0
         5          8          1          5          3
        10         18          1         10          8
        20         18          0          0         18
        50         18          0          0         18
       100        118          1        100         18

6 rows selected. 

The code provided by Tony is great. but how to do the same in oracle 9i. please answer. below is the sql provided by tony.

variable x number;

exec :x := 1286;

select
denomination, amt, result from (
select
denomination,
amt,
row_number() over (order by amt desc) rn
from money ) m
model
dimension by (rn)
measures ( cast(null as number) remainder,
cast(null as number) prev_remainder,
cast(null as number) result,
amt,
denomination)
RULES
(
remainder[rn] order by rn =
mod(
presentnnv( remainder[cv(rn) - 1], remainder[cv(rn) - 1], :x),
amt[cv(rn)]),
prev_remainder[rn] order by rn =
presentnnv( remainder[cv(rn) - 1], remainder[cv(rn) - 1], :x),
result[rn] = ( prev_remainder[cv(rn)] - remainder[cv(rn)] ) / amt[cv(rn)]
)

Hi Nagaraj, here's your solution with analytic functions (plus connect by) and without MODEL; better ones are certainly possible!.
However I can't think of any practical use of this stuff (it's really slow to start with); I'd rather write some clean pl/sql.

The query combinatorially generates solutions, and the "most sensible" is returned.


variable x number;

exec :x := 1286;

with max_cash as (
select
m.*,
case when rn = 1 then trunc(:x / amt) /* the qty of the most valued coin is
calculated first, and printed at the end */
else
case
prev_amt/amt when trunc(prev_amt/amt) then
trunc(prev_amt/amt) - 1
else trunc(prev_amt/amt)
end
end max_coins /* this gives the max qty of this coin
in the final result: e.g if I have both 5$ bills and 1$bills,
I'll use at most 4 1$ bills
*/
from (select row_number() over (order by amt desc ) rn,
max(amt) over () max_amt,
lag (amt) over (order by amt desc) prev_amt,
m.* from money m) m
),
money_paths as ( /* I find all the available coin combinations,
then I'll filter just the ones which give the correct result */
select sys_connect_by_path(contrib,'+')||'+' scbp,
denomination,
amt,
num_coins,
contrib,
target
from (
select (select mod(:x,amt) from max_cash where rn = 1) target, x.*,
int_generator.rn num_coins, (amt * int_generator.rn) contrib
from max_cash x,
(
select rownum -1 rn from all_objects
where rownum < 100 /* I assume
consecutive coin ratios are within this range,
it should be made more general ... */
) int_generator
where x.max_coins >= int_generator.rn
and x.rn > 1
) start with rn = 2
connect by prior rn = rn - 1
) ,
results as (
select * from (
select res.* from (
select h.*,
(select sum(h2.contrib)
from money_paths h2 where h.scbp like h2.scbp||'%' ) total
from money_paths h
) res where total = target
order by res.scbp desc
) where rownum < 2 -- I choose only the first of the many possible solutions
)
select denomination, amt, max_coins num_coins from max_cash where rn = 1
union all
select mp.denomination, mp.amt, mp.num_coins
from
results, money_paths mp
where results.scbp like mp.scbp||'%'
;

Tony-- Thank you, I'd been meaning to look at how the MODEL clause works, and your example was quite helpful.  Here I've re-cast it to produce the same results ( + rn ) as my earlier query... now I just need to focus on what the presentnnv, cv and other interrow functions are actually doing...
-- Duke

select amt
     , "start"
     , "# of bills"
     , amt*"# of bills" AS "$ of bills"
     , "remainder"
     , rn
  from (
   select denomination,
          amt,
          row_number() over (order by amt desc) rn
    from money ) m
model
    dimension by (rn)
    measures ( cast(null as number) "remainder",
               cast(null as number) "start",
               cast(null as number) "# of bills",
               amt,
               denomination
             )
RULES
    (
    "remainder"[rn] order by rn =
        mod(
               presentnnv( "remainder"[cv(rn) - 1]
                         , "remainder"[cv(rn) - 1]
                         , :x
                         )
             , amt[cv(rn)]
           ),
    "start"[rn] order by rn =
             presentnnv( "remainder"[cv(rn) - 1]
                       , "remainder"[cv(rn) - 1]
                       , :x),
    "# of bills"[rn] =
              ( "start"[cv(rn)] - "remainder"[cv(rn)] )
              / amt[cv(rn)]
    )
ORDER BY amt
SQL> /

  AMT      start # of bills $ of bills  remainder         RN
----- ---------- ---------- ---------- ---------- ----------
    1          3          3          3          0          6
    5          8          1          5          3          5
   10         18          1         10          8          4
   20         18          0          0         18          3
   50         18          0          0         18          2
  100        118          1        100         18          1

6 rows selected. 

And with some reading of the MODEL options and functions, it can be made a bit more concise and readable by using AUTOMATIC ORDER, and substituting NVL for PRESENTNNV...

select amt
     , "start"
     , "# of bills"
     , amt*"# of bills" AS "$ of bills"
     , "remainder"
     , rn
  from (
   select denomination,
          amt,
          row_number() over (order by amt desc) rn
    from money ) m
model
    dimension by (rn)
    measures ( cast(null as number) "remainder",
               cast(null as number) "start",
               cast(null as number) "# of bills",
               amt,
               denomination
             )
RULES AUTOMATIC ORDER
    (
    "start"[rn] order by rn = -- LAG remainder
        NVL( "remainder"[cv(rn) - 1], :x )
  , "remainder"[rn] =
        mod( "start"[cv(rn)], amt[cv(rn)] )
  , "# of bills"[rn] =
        ( "start"[cv(rn)] - "remainder"[cv(rn)] )
        / amt[cv(rn)]
    )
ORDER BY amt
SQL> /

  AMT      start # of bills $ of bills  remainder         RN
----- ---------- ---------- ---------- ---------- ----------
    1          3          3          3          0          6
    5          8          1          5          3          5
   10         18          1         10          8          4
   20         18          0          0         18          3
   50         18          0          0         18          2
  100        118          1        100         18          1

6 rows selected. 

I have a database table where the data is like this

COIN_SLNO COIN_FACTOR COIN_DESC
--------------- --------------- ---------
1 1000 1000's
2 500 500's
3 200 200's
4 100 100's
5 50 50's
6 20 20's
7 10 10's
8 5 5's
9 1 1's

but when i run this sql
SELECT COIN_SLNO, COIN_FACTOR, COIN_DESC,
LAG(MOD(1886, COIN_FACTOR)) OVER
(ORDER BY COIN_SLNO) LAG_AMT
FROM COMM_COINAGE
/

the output i get is like this

COIN_SLNO COIN_FACTOR COIN_D LAG_AMT
--------------- --------------- ------ ---------------
1 1000 1000's
2 500 500's 886
3 200 200's 386
4 100 100's 86
5 50 50's 86
6 20 20's 36
7 10 10's 6
8 5 5's 6
9 1 1's 1

if u notice slno 4 the lag_amt should have been 186 and not 86 and in slno 7 it should have been 16 and not 6. why is this happening only for coin_factor 200 & 20 i.e. multiples of 2.

"U"??  who is that, and why do people keep repeatedly asking for them to look at stuff??



If you notice, the lag of coin_factor ordered by coin_slno for slno = 4 is 200.


ops$tkyte@ORA10GR2> select mod(1886, 200 ) from dual;

MOD(1886,200)
-------------
           86

so I disagree that it should have been 186.

why do you think otherwise? 

create table tt as
select trunc(sysdate-level) dt,level num from dual connect by level<=10;

update tt set num=null;

select dt,row_number() over (order by dt) rn from tt;

How can I assign that "rn" to each row in the table? Basically, I need to update the rows in the table and assign them the row_number() (i.e. order the rows by dt)

I need something like

update tt set num=row_number() over (order by dt);

But obviously that doesn't work.

Any ideas?

Thanks

ops$tkyte%ORA10GR2> select * from tt order by dt;

DT               NUM
--------- ----------
29-AUG-06
30-AUG-06
31-AUG-06
01-SEP-06
02-SEP-06
03-SEP-06
04-SEP-06
05-SEP-06
06-SEP-06
07-SEP-06

10 rows selected.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> merge into tt
  2  using (select rowid rid, row_number() over (order by dt) rn from tt) new_tt
  3  on (tt.rowid = new_tt.rid)
  4  when matched then update set num = rn;

10 rows merged.

ops$tkyte%ORA10GR2> select * from tt order by dt;

DT               NUM
--------- ----------
29-AUG-06          1
30-AUG-06          2
31-AUG-06          3
01-SEP-06          4
02-SEP-06          5
03-SEP-06          6
04-SEP-06          7
05-SEP-06          8
06-SEP-06          9
07-SEP-06         10

10 rows selected.

 

On Oracle 9.2.0.6, I get

SQL> l
  1  merge into tt
  2  using (select rowid rid, row_number() over (order by dt) rn from tt) new_tt
  3  on (tt.rowid = new_tt.rid)
  4* when matched then update set num = rn
SQL> /
when matched then update set num = rn
                                    *
ERROR at line 4:
ORA-00905: missing keyword

I guess that is because it needs the INSERT part for the MERGE as well?

Any ideas for 9.2.0.6? We are not on 10g yet

Thanks 

There are at least 2 solutions: you can add a dummy "not matched" part or you use the good old update:

SQL> select * from tt order by dt;
DT                 NUM
----------- ----------
31-AUG-2006
01-SEP-2006
02-SEP-2006
03-SEP-2006
04-SEP-2006
05-SEP-2006
06-SEP-2006
07-SEP-2006
08-SEP-2006
09-SEP-2006

10 rows selected.

SQL> merge into tt 
  2  using (select rowid rid, dt, 
  3                row_number() over (order by dt) rn from tt) new_tt
  4  on (tt.rowid = new_tt.rid)
  5  when matched then update set num = rn
  6  when not matched then insert (dt) values(new_tt.dt)
  7  /

10 rows merged.

SQL> select * from tt order by dt;
DT                 NUM
----------- ----------
31-AUG-2006          1
01-SEP-2006          2
02-SEP-2006          3
03-SEP-2006          4
04-SEP-2006          5
05-SEP-2006          6
06-SEP-2006          7
07-SEP-2006          8
08-SEP-2006          9
09-SEP-2006         10

10 rows selected.

SQL> rollback;

Rollback complete.

SQL> update tt a
  2  set num = ( select rn
  3              from ( select rowid row_id,
  4                            row_number () over (order by dt) rn
  5                     from tt ) b
  6              where b.row_id = a.rowid )
  7  /

10 rows updated.

SQL> select * from tt order by dt;
DT                 NUM
----------- ----------
31-AUG-2006          1
01-SEP-2006          2
02-SEP-2006          3
03-SEP-2006          4
04-SEP-2006          5
05-SEP-2006          6
06-SEP-2006          7
07-SEP-2006          8
08-SEP-2006          9
09-SEP-2006         10

10 rows selected.

Michel 

Thanks, Tom and Michael.

One more question, if you don't mind.

select dt,row_number() over (order by dt) rn from tt;

This assigns row_numbers() to the entire table. I would like to know the row_number() it assigns to a specific dt value. If I simply do

select dt,rn from (
select dt,row_number() over (order by dt) rn
from tt
)
where dt=...

it will always give 1 because the predicate is pushed inside the query so it is working with 1 row only.

How can I get it to assign row numbers to the entire table and then pluck out my desired dt?

Thanks

Sorry, my mistake, I just assumed that it would push the predicate. You are right, of course.

But even though the predicate is not pushed, does it do any optimization or does it assign row numbers to the entire table tt in temp space and then return that one row? i.e. does it do any Top-N style optimization

Thanks a million.

I was just curious, I understand that it will not be efficient.

I will be using the MERGE you showed earlier to assign row numbers to the entire table. Actually, they are not simply row numbers, the process is re-assigning new identifiers to a table based on some existing attributes and one of the requirements is to assign that row_number() in order of some existing date column.

This will be one-time bulk update of the table, I was just curious if it was possible to get a quick "sneak preview" of the new value for a specific row. But I see that the nature of the problem is such that it has to sort the entire set before it can begin to assign the row numbers.

Thanks

This moneychanging question, and especially Tony's use of the MODEL clause, was the basis for my recent entry on the MODEL clause -- see either
http://tinyurl.com/2jy5dg
or
http://blogs.ittoolbox.com/dw/ganote/archives/better-than-a-spreadsheet-sql-with-model-clause-14703
Thank you all!

When I saw this interesting problem, I forwarded it to a few of my colleagues and one of them, (Velu) produced quite a neat bit of sql..
I am sharing it here because I think it simply blows away all the solutions here ..

script:

CREATE TABLE MONEY
(
DENOMINATION CHAR(25 BYTE) NOT NULL,
AMT INTEGER NOT NULL
)
/
INSERT INTO MONEY ( DENOMINATION, AMT ) VALUES (
'one pound note ', 1);
INSERT INTO MONEY ( DENOMINATION, AMT ) VALUES (
'five pound note ', 5);
INSERT INTO MONEY ( DENOMINATION, AMT ) VALUES (
'ten pound note ', 10);
INSERT INTO MONEY ( DENOMINATION, AMT ) VALUES (
'twenty pound note ', 20);
INSERT INTO MONEY ( DENOMINATION, AMT ) VALUES (
'fifty pound note ', 50);
COMMIT;

select
a.amt amt,
a.denomination denomination,
trunc((select nvl(min(mod(:cash, b.amt)), :cash) from money b where b.amt > a.amt)/a.amt) num_notes
from money a
/

Thanks,
Shailendra

Hi All,
I accidentally saw this Puzzle when I was browsing asktom today. Both the posted problem and solution given by Tom are very intresting.
Tom's knack of solving is simply superb and every time I look at Tom's site I certainly learn something new.
One of the intriguing thing was the simple solution put by Shailendra from his colleague, eventhough it is very elegant, simple and thought provoking. It turned out to be wrong. When I tried the solution for $188, the query resulted in a wrong answer.
Hats off to you Tom.
Thanks

Velu's approach works for $1 to $59. Starts failing at $60:

variable cash number;

exec :cash := 60;

select a.amt,
a.denomination,
trunc((select nvl(min(mod(:cash, b.amt)), :cash)
from money b
where b.amt > a.amt
)/a.amt
) num_notes
from money a
/
AMT DENOMINATION NUM_NOTES
----- ------------------------- ----------
1 one pound note 0
5 five pound note 0
10 ten pound note 0
20 twenty pound note 0
50 fifty pound note 1

Hi Tom,
I have re-wrote the SQL below using analytics ,to me both are same but want to know if understanding is correct ( I could not compare the outputs as database went down for some maintainence work) before I could test the outputs.

original SQL

SELECT RPAD(msib.segment1,25) segment1
,RPAD(NVL(msib.description, ' '),30) description
,RPAD(NVL(mirb.revision, ' '),3) revision
,RPAD(DECODE(msib.build_in_wip_flag,'Y',
DECODE(NVL(bor.completion_subinventory,'-N'),'-N','N','Y'),'N'),1) post_deductable_flag
,RPAD(CASE WHEN mirb.effectivity_date >SYSDATE THEN 'N' ELSE 'Y' END,1) cur_rev_flag
FROM BOM.bom_operational_routings bor
,INV.mtl_item_revisions_b mirb
,GEMS_APPS.gems_org_attributes goa
,INV.mtl_system_items_b msib
,apps.org_organization_definitions ood
WHERE msib.enabled_flag = 'Y'
AND msib.serial_number_control_code IN (2,5,6)
AND mirb.organization_id = ood.organization_id
AND ood.organization_id = goa.organization_id
-- To check for eDHR enabled organizations
AND UPPER(goa.attribute18) = 'EDHR'
AND goa.organization_type = 'I'
AND msib.inventory_item_id = mirb.inventory_item_id
AND msib.organization_id = mirb.organization_id
AND mirb.effectivity_date =
(SELECT MAX(effectivity_date)
FROM INV.mtl_item_revisions_b mirb2
WHERE mirb2.organization_id = mirb.organization_id
AND mirb2.inventory_item_id = mirb.inventory_item_id
AND effectivity_date <= SYSDATE)
AND mirb.last_update_date >= SYSDATE - NVL(v_days_to_extract,'1.15')
AND msib.inventory_item_id = bor.assembly_item_id(+)
AND msib.organization_id = bor.organization_id(+)

Modified SQL

select * from
(
SELECT RPAD(msib.segment1,25) segment1
,RPAD(NVL(msib.description, ' '),30) description
,RPAD(NVL(mirb.revision, ' '),3) revision
,RPAD(DECODE(msib.build_in_wip_flag,'Y',
DECODE(NVL(bor.completion_subinventory,'-N'),'-N','N','Y'),'N'),1) post_deductable_flag
,RPAD(CASE WHEN mirb.effectivity_date >SYSDATE THEN 'N' ELSE 'Y' END,1) cur_rev_flag
, max(mirb.effectivity_date) over ( partition by mirb.organization_id,mirb.inventory_item_id) max_date,
mirb.effectivity_date
FROM BOM.bom_operational_routings bor
,INV.mtl_item_revisions_b mirb
,GEMS_APPS.gems_org_attributes goa
,INV.mtl_system_items_b msib
,apps.org_organization_definitions ood
WHERE msib.enabled_flag = 'Y'
AND msib.serial_number_control_code IN (2,5,6)
AND mirb.organization_id = ood.organization_id
AND ood.organization_id = goa.organization_id
-- To check for eDHR enabled organizations
AND UPPER(goa.attribute18) = 'EDHR'
AND goa.organization_type = 'I'
AND msib.inventory_item_id = mirb.inventory_item_id
AND msib.organization_id = mirb.organization_id
AND mirb.effectivity_date <= sysdate
AND mirb.last_update_date >= SYSDATE - NVL(v_days_to_extract,'1.15')
AND msib.inventory_item_id = bor.assembly_item_id(+)
AND msib.organization_id = bor.organization_id(+)
)
where max_date = effectivity_date

--description of changes

removed the following code from origianl SQL

AND mirb.effectivity_date = (SELECT MAX(effectivity_date)
FROM INV.mtl_item_revisions_b mirb2
WHERE mirb2.organization_id = mirb.organization_id
AND mirb2.inventory_item_id = mirb.inventory_item_id
AND mirb2.effectivity_date <= SYSDATE)

and put the
max(mirb.effectivity_date) over ( partition by mirb.organization_id,mirb.inventory_item_id) max_date in select clause

and then finally a
where clause max_date = effectivity_date
and in the iner query added the condition

mirb.effectivity_date <= sysdate

Thank you.

Tom,
Can of the approaches above be used to solve a problem where I want to use analytics to find the maximum of a computed value, where the computation is based on a formula that uses values from the current row and the other rows. For example, lets say I have columns x and y ordered by timestamp in my table.

I can easily find the max values of x and y over the next minute for each row in the table using:

select ts, x, y,
max(x) over (order by ts range between current row and 1/(60*24) following) max_x_next_minute,
max(y) over (order by ts range between current row and 1/(60*24) following) max_y_next_minute
from coords

but what if I don't want the max of x and y I want the max of my own formula like one that would be the distance between two points such as

sqrt(power(x2 - x1,2) + power(y2 - y1,2))

where x2,y2 would be the coordinates from each of the rows over the next minute and x1,y1 is the coordinate of the row being evaluated. Essentially I'm trying to figure out how to reference the current row in my analytic function but can't (or at least I don't know how).

I'm hoping to have something that looks more like:

select ts, x, y,
max(sqrt(power(x2 - x1,2) + power(y2 - y1,2))) over (order by ts range between current row and 1/(60*24) following) max_dist_next_minute
from coords

Obviously I can join the table to itself and perform a cartesian and limit to rows where the timestamp is within the next minute but that performs horribly and seems to be exactly why I should be using analytics instead. I've seen some info on the model clause but have never used it and am not sure this is a candidate for that or not.

Any suggestions?

on further review... am I just overthinking the problem trying to force my way into analytics and should I really just be doing it the old fashioned way like this:

select a.ts, a.x, a.y,
(select max(sqrt(power(x-a.x,2) + power(y-a.y,2)))
from coords
where ts < a.ts + 1/60/24)
from coords

For some reason I was thinking that would perform worse than an analytic approach (which seems pretty irrelevant since I can't find an analytic approach) but I'm starting to think this may be the way to go.

hi,,,,

how i can do that if bigTable have 2,000,000,000 records

select col1 , col2 , col3 , colDate
(select max(colDate)x from bigtable b
where a.col1 =b.col1
and a.col2= b.col2
and col3 = '+'
and a.colDate > b.colDate) myRequestDate
from bigtable a
where 1=1 and ......
and col3 = '-'


i can get result bu sooooo slow,,,

ok .. let say i have this table

Create table XX_Table -- bigTable
(id_col1 varchar2(5) , -- id1
id_col2 varchar2(5) , -- id2
id_col3 varchar2(5) , -- id3 // id1 + id2 + id3 represent an unique account in specific area.
balance_col4 number ,
area_col5 varchar2(3),
colDate date
) ;

** Notes :
-- Data are repeated on a daily basis with the possibility of a change in the balance.(colGate = colGate 1).
-- i have a unique index on XX_Table (area_col4 + colDate + id_col1 + id_col2 + id_col3).
-- PARTITION BY RANGE (colDate) .
-- SUBPARTITION BY LIST (area_col4).


*** just i need your help to do the below select :

/*

Select id_col1 , id_col2 , id_col3 , colDate,
(select max(colDate)x from XX_Table b
where a.id_col1 =b.id_col1
and a.id_col2= b.id_col2
and a.id_col3 = b.id_col3
and a.area_col5 =b.area_col5
and balance_col4 >= 0
and a.colDate > b.colDate) myRequestDate
from XX_Table a
where 1=1 and area_col5 = 'AR1'
and a.colDate = trunc(sysdate)
balance_col4 < 0 ;

*/

-- i need to know the latest date the account was a positive balance if it now have a negative balance.


i dont have any problem with resources , every thing is ok . i can deal with this table
and i wrote this query in different ways but still not found the best one ..
as i expect from you ..

thanks for your help...

ops$tkyte%ORA11GR2> insert into t values ( 1, 1, 1,  101, 'AR1', trunc(sysdate)-4 );

1 row created.

ops$tkyte%ORA11GR2> insert into t values ( 1, 1, 1,  201, 'AR1', trunc(sysdate)-3 );

1 row created.

ops$tkyte%ORA11GR2> insert into t values ( 1, 1, 1,  301, 'AR1', trunc(sysdate)-2 );

1 row created.

ops$tkyte%ORA11GR2> insert into t values ( 1, 1, 1,  401, 'AR1', trunc(sysdate)-1 );

1 row created.

ops$tkyte%ORA11GR2> insert into t values ( 1, 1, 1, -501, 'AR1', trunc(sysdate)-0 );

1 row created.

ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> insert into t values ( 2, 1, 1,  102, 'AR1', trunc(sysdate)-4 );

1 row created.

ops$tkyte%ORA11GR2> insert into t values ( 2, 1, 1,  202, 'AR1', trunc(sysdate)-3 );

1 row created.

ops$tkyte%ORA11GR2> insert into t values ( 2, 1, 1, -302, 'AR1', trunc(sysdate)-2 );

1 row created.

ops$tkyte%ORA11GR2> insert into t values ( 2, 1, 1, -402, 'AR1', trunc(sysdate)-1 );

1 row created.

ops$tkyte%ORA11GR2> insert into t values ( 2, 1, 1, -502, 'AR1', trunc(sysdate)-0 );

1 row created.

ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> insert into t values ( 3, 1, 1,  103, 'AR1', trunc(sysdate)-4 );

1 row created.

ops$tkyte%ORA11GR2> insert into t values ( 3, 1, 1,  203, 'AR1', trunc(sysdate)-3 );

1 row created.

ops$tkyte%ORA11GR2> insert into t values ( 3, 1, 1, -303, 'AR1', trunc(sysdate)-2 );

1 row created.

ops$tkyte%ORA11GR2> insert into t values ( 3, 1, 1,  403, 'AR1', trunc(sysdate)-1 );

1 row created.

ops$tkyte%ORA11GR2> insert into t values ( 3, 1, 1,  503, 'AR1', trunc(sysdate)-0 );

1 row created.

ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> select *
  2    from (
  3  select id_col1, id_col2, id_col3, balance_col4, colDate,
  4         last_value( case when balance_col4 > 0 then colDate end ignore nulls )
  5         over (partition by id_col1, id_col2, id_col3 order by colDate ) last_positive_balance
  6    from t
  7   where ((colDate = trunc(sysdate) and balance_col4 < 0)
  8          OR
  9          (balance_col4 > 0)
 10         )
 11         and area_col5 = 'AR1'
 12             )
 13   where colDate = trunc(sysdate)
 14     and balance_col4 < 0
 15  /

ID_CO ID_CO ID_CO BALANCE_COL4 COLDATE   LAST_POSI
----- ----- ----- ------------ --------- ---------
1     1     1             -501 07-JUN-11 06-JUN-11
2     1     1             -502 07-JUN-11 04-JUN-11