This discussion is archived
8 Replies Latest reply: Dec 11, 2012 12:57 PM by Frank Kulash RSS

Max Price Based on Date

798631 Newbie
Currently Being Moderated
Hello,

I need to return the price of the id having the max(date).There can be multiple identical id's with different prices with diffent dates. The date is and must be <= '10-10-12'.

ID     Price     Dt
1234 10 09-09-12
1234     20     09-20-12
1234 15 15-10-12

I am trying to return 20, but keep getting both 10 and 20 since they are both <= 10-10-12

select id,price,dt
from table_name
where id = 1234
group by id,price,date
having max(dt) <= to_date('10-oct-2012')

How can i write this to return only 20?

Edited by: DOUBLE U on Dec 11, 2012 2:31 PM -- Changed occurences of date and action date to dt. Action date was an error.
  • 1. Re: Max Price Based on Date
    Frank Kulash Guru
    Currently Being Moderated
    Hi,
    DOUBLE U wrote:
    Hello,

    I need to return the price of the id having the max(date).There can be multiple identical id's with different prices with diffent dates. The date is and must be <= '10-10-12'.

    ID     Price     Date
    1234 10 09-09-12
    1234     20     09-20-12
    1234 15 15-10-12

    I am trying to return 20, but keep getting both 10 and 20 since they are both <= 10-10-12

    select id,price,date
    DATE is an Oracle reserved word; it's not a good name for your own columns. I used dt instead. (It's unclear if this is the same column as action_date or not.)
    from table_name
    where id = 1234
    group by id,price,date
    If you want the result set to have only 1 row per id, then you should GROUP BY id, and nothing else.
    having max(action_date) <= to_date('10-oct-2012')
    Never call TO_DATE with only 1 argument. Always give the format explicitly.
    How can i write this to return only 20?
    You can use the LAST function for that:
    SELECT       id
    ,       MAX (price) KEEP (DENSE_RANK LAST ORDER BY dt)
                             AS last_price
    ,       MAX (dt)         AS last_dt 
    FROM       table_x
    GROUP BY  id
    HAVING       MAX (action_date)     <= TO_DATE ( '10-Oct-2012'
                                         , 'DD-Mon-YYYY'
                                )
    ;
     

    I hope this answers your question.
    If not, post a little sample data (CREATE TABLE and INSERT statements, relevant columns only) for all tables involved, and also post the results you want from that data.
    Explain, using specific examples, how you get those results from that data.
    Always say which version of Oracle you're using (e.g., 11.2.0.2.0).
    See the forum FAQ {message:id=9360002}
  • 2. Re: Max Price Based on Date
    798631 Newbie
    Currently Being Moderated
    Hmmm.

    This seems to be returning the oldest date. 09/09/12
  • 3. Re: Max Price Based on Date
    Frank Kulash Guru
    Currently Being Moderated
    Hi,
    DOUBLE U wrote:
    Hmmm.

    This seems to be returning the oldest date. 09/09/12
    I could have made some silly mistake since I can't test it.
    If you'd care to post CREATE TABLE and INSERT statements for your sample data, then I could test this.
    Also, I still don't know if dt and action_date are the same column or not.
  • 4. Re: Max Price Based on Date
    Hoek Guru
    Currently Being Moderated
    DOUBLE U wrote:
    Hello,

    I need to return the price of the id having the max(date).There can be multiple identical id's with different prices with diffent dates. The date is and must be <= '10-10-12'.

    ID     Price     Date
    1234 10 09-09-12
    1234     20     09-20-12
    1234 15 15-10-12

    I am trying to return 20, but keep getting both 10 and 20 since they are both <= 10-10-12

    select id,price,date
    from table_name
    where id = 1234
    group by id,price,date
    having max(action_date) <= to_date('10-oct-2012')

    How can i write this to return only 20?
    Why are you using only 2 digits for the year part? Why not 4?
    And what DATE FORMAT are you using? Is it dd-mm-rr or mm-dd-rr or is it dd-mm-yy or mm-dd-yy?
    09-20-12 and 15-10-12?
    Your 'data' makes no real sense at all.
    And where does column action_date come from?
    Next time post CREATE TABLE and INSERT INTO statements.
  • 5. Re: Max Price Based on Date
    798631 Newbie
    Currently Being Moderated
    Based on the the data in test_table (create and insert statements below) i am trying to return 20.

    select id,price,dt
    from wr_test_table
    where id = 1234
    group by id,price,dt
    having max(dt) <= to_date('10-oct-2012')


    create table test_table (
    id number,
    price number,
    dt date
    );

    insert into test_table values
    ('1234','10','09-sep-2012');
    insert into test_table values
    ('1234','20','20-sep-2012');
    insert into test_table values
    ('1234','15','15-oct-2012');
  • 6. Re: Max Price Based on Date
    Solomon Yakobson Guru
    Currently Being Moderated
    DOUBLE U wrote:
    This seems to be returning the oldest date. 09/09/12
    Use:
    with table_name as (
                        select 1234 id,10 price,to_date('09-09-12','mm-dd-yy') dt from dual union all
                        select 1234,20,to_date('09-20-12','mm-dd-yy') from dual union all
                        select 1234,15,to_date('10-15-12','mm-dd-yy') from dual
                       )
    select  id,
            max(price) keep(dense_rank last order by dt) price,
            max(dt) dt
      from  table_name
      where dt <= to_date('10-10-12','mm-dd-yy')
      group by id
    /
    
            ID      PRICE DT
    ---------- ---------- ---------
          1234         20 20-SEP-12
    
    SQL>  
    SY.
  • 7. Re: Max Price Based on Date
    sb92075 Guru
    Currently Being Moderated
    DOUBLE U wrote:
    Based on the the data in test_table (create and insert statements below) i am trying to return 20.

    select id,price,dt
    from wr_test_table
    where id = 1234
    group by id,price,dt
    having max(dt) <= to_date('10-oct-2012')
    above is incorrect usage of TO_DATE('10-oct-2012','DD-MON-YYYY')

    >
    >
    create table test_table (
    id number,
    price number,
    dt date
    );

    insert into test_table values
    ('1234','10','09-sep-2012');
    insert into test_table values
    ('1234','20','20-sep-2012');
    insert into test_table values
    ('1234','15','15-oct-2012');
    above are STRINGS; not DATES
  • 8. Re: Max Price Based on Date
    Frank Kulash Guru
    Currently Being Moderated
    Hi,
    DOUBLE U wrote:
    Based on the the data in test_table (create and insert statements below) i am trying to return 20.

    select id,price,dt
    from wr_test_table
    where id = 1234
    group by id,price,dt
    having max(dt) <= to_date('10-oct-2012')
    Did you read my first message? This has the same mistakes I corrected before.
    If you want the output to contain 1 row per id (that is, 1 row total in this case) then the GROUP BY clause should be
    GROUP BY  id
    with nothing else. If you say
    GROUP BY  id, price, dt
    then the result set will have a separated row for each distinct combination of id, price and dt.

    Also, when you call TO_DATE to should always include at least 2 arguments. Calling TO_DATE with just 1 argument is simply asking for trouble.
    create table test_table (
    id number,
    price number,
    dt date
    );
    Okay, so the table has 2 NUMBER columns and 1 DATE column. There are no VARCHAR2 columns.
    insert into test_table values
    ('1234','10','09-sep-2012');
    insert into test_table values
    ('1234','20','20-sep-2012');
    insert into test_table values
    ('1234','15','15-oct-2012');
    You're trying to INSERT 3 VARCHAR2 values.
    Don't put single-quotes around NUMBER literals.
    Use TO_DATE to convert a VARCHAR2 value, such as '09-sep-2012', into a DATE.
    For example:
    insert into test_table (id, price, dt) values
    (1234, 10, TO_DATE ('09-sep-2012', 'DD-mon-YYYY'));
    If dt and action_dt are the same column, then you probably want to test for dt being early enough in a WHERE clause, where it will exclude just individual rows, not in a HAVING clause, where it will exclude the entire group. I think this is what you want:
    SELECT       id
    ,       MAX (price) KEEP (DENSE_RANK LAST ORDER BY dt)
                             AS last_price
    ,       MAX (dt)         AS last_dt 
    FROM       table_x
    WHERE       dt     <= TO_DATE ( '10-Oct-2012'
                            , 'DD-Mon-YYYY'
                      )
    GROUP BY  id
    ;
    Output:
    `  ID LAST_PRICE LAST_DT
    ----- ---------- -----------
     1234         20 20-Sep-2012

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points