Results 1 to 3 of 3

Thread: qry req

  1. #1
    Join Date
    Feb 2004
    Location
    Chennai
    Posts
    53

    Unanswered: qry req

    I need a query for the following

    A table with two fields

    RATE Date
    10 20-jul-04
    10 13-jul-04
    10 05-jul-04
    11 20-jun-04
    11 10-jun-04
    9.1 03-jun-04
    10 20-may-04


    I need a query to fetch the date when the latest rate has been changed
    ie,05-jul-04

    pls help
    -Mathan
    For a quick pocket reference of oracle refer http://pocketoracle.blogspot.com/

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    This query will do it. I called the table T1 and renamed the "date" column to RATEDATE, since "date" is a reserved word.
    Code:
    SQL> select max(ratedate)
      2  from
      3  (
      4  select rate, ratedate, lag(rate) over (order by ratedate) prevrate
      5  from t1
      6  )
      7  where (prevrate is null or rate != prevrate);
    
    MAX(RATEDAT
    -----------
    05-JUL-2004

  3. #3
    Join Date
    Feb 2004
    Location
    Chennai
    Posts
    53
    thanks tony
    -Mathan
    For a quick pocket reference of oracle refer http://pocketoracle.blogspot.com/

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •