Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

SQL Help, Pick highest Spend

Kodiak_SeattleJan 12 2010 — edited Jan 14 2010
Oralce 10G.

I have a Table with many unique records, kinda looks like this:

customer_key | BB | CL | LA
123 100 300 50
456 65 125 658
789 12 5 98

I need to take the highest spend.

customer_key | SPEND
123 300
456 658
789 98

Not sure how do this since, the field have different names ?
This post has been answered by Frank Kulash on Jan 12 2010
Jump to Answer

Comments

105967

If I understand you correctly you need:

SQL> get t
  1  with td as ( select to_date('27.11.07 06:00', 'dd.mm.yyyy hh24:mi') my_date, 1 id from dual union all
  2               select to_date('27.11.07 10:00', 'dd.mm.yyyy hh24:mi'), 2 from dual union all
  3               select to_date('27.11.07 10:00', 'dd.mm.yyyy hh24:mi'), 1 from dual union all
  4               select to_date('27.11.07 10:30', 'dd.mm.yyyy hh24:mi'), 2 from dual union all
  5               select to_date('27.11.07 10:45', 'dd.mm.yyyy hh24:mi'), 1 from dual union all
  6               select to_date('27.11.07 15:00', 'dd.mm.yyyy hh24:mi'), 2 from dual
  7             )
  8  -- end of testdata
  9  select to_char(max(my_date), 'dd.mm.yyyy hh24:mi')
 10  from   td
 11  where  id = 2
 12  and    my_date < ( select max(my_date)
 13                     from   td
 14                     where  id = 1
 15                     group by id
 16                   )
 17* group by id
SQL> /
 
TO_CHAR(MAX(MY_D
----------------
27.11.0007 10:30
 
SQL> 
21205
with x as 
(
select to_date ('27.11.07 06:00', 'dd.mm.rr hh24:mi') dt, 1 type from dual union all
select to_date ('27.11.07 10:00', 'dd.mm.rr hh24:mi') dt, 2 type from dual union all
select to_date ('27.11.07 10:00', 'dd.mm.rr hh24:mi') dt, 1 type from dual union all
select to_date ('27.11.07 10:30', 'dd.mm.rr hh24:mi') dt, 2 type from dual union all
select to_date ('27.11.07 10:45', 'dd.mm.rr hh24:mi') dt, 1 type from dual union all
select to_date ('27.11.07 15:00', 'dd.mm.rr hh24:mi') dt, 2 type from dual
)
select max_value
     , (select max (dt) 
          from x 
         where type = 2
           and dt < max_value
       ) min_value
  from (
select max (dt) max_value
  from x
 where type = 1
 ) 
;
Dip
I need to get both: 10:30 and 10:45.
jeneesh

or:

SQL> select * from td;

MY_DATE                  ID
---------------- ----------
27/11/0007 06:00          1
27/11/0007 10:00          2
27/11/0007 10:00          1
27/11/0007 10:30          2
27/11/0007 10:45          1
27/11/0007 15:00          2

6 rows selected.

SQL> select max(decode(id,1,my_date)) mx1,
  2         max(decode(id,2,my_date)) mx2
  3  from ( select my_date,id,max(decode(id,1,my_date)) over(order by null) mx
  4           from td)
  5  where my_date < mx
  6  or id = 1;

MX1              MX2
---------------- ----------------
27/11/0007 10:45 27/11/0007 10:30                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    
Dip
So i need to use two select statements.

I knew that i could do that.
But i thought, there is some shorter way to get what i want (because i have long SQL statement).


Thanks.
Aketi Jyuuzou
with td as (
select to_date('27.11.07 06:00', 'dd.mm.yyyy hh24:mi') my_date, 1 id from dual union
select to_date('27.11.07 10:00', 'dd.mm.yyyy hh24:mi'), 2 from dual union
select to_date('27.11.07 10:00', 'dd.mm.yyyy hh24:mi'), 1 from dual union
select to_date('27.11.07 10:30', 'dd.mm.yyyy hh24:mi'), 2 from dual union
select to_date('27.11.07 10:45', 'dd.mm.yyyy hh24:mi'), 1 from dual union
select to_date('27.11.07 15:00', 'dd.mm.yyyy hh24:mi'), 2 from dual)
select to_char(TargetMy_date,'dd.mm.yyyy hh24:mi') as TargetMy_date,
to_char(ID2,'dd.mm.yyyy hh24:mi') as ID2
from (select my_date,id,
      max(decode(ID,1,my_date)) over () as TargetMy_date,
      Last_Value(case when ID = 2 then my_date end ignore nulls) over(order by my_date) as ID2
        from td)
 where id = 1
   and my_date = TargetMy_date;
1 - 6
Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on Feb 11 2010
Added on Jan 12 2010
6 comments
1,204 views