8 Replies Latest reply: Dec 11, 2012 2:57 PM by Frank Kulash RSS

    Max Price Based on Date

    798631
      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
          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
            Hmmm.

            This seems to be returning the oldest date. 09/09/12
            • 3. Re: Max Price Based on Date
              Frank Kulash
              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
                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
                  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
                    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
                      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
                        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