Results 1 to 2 of 2
  1. #1
    Join Date
    Aug 2008
    Posts
    464

    Unanswered: Writing a query - 10g

    Hi

    I have an table containing hourly values and I want to write a query to extract a couple of columns based on the maximum value of one of the extracted columns. The query I wrote is:

    Code:
    SELECT * FROM                                                
    (SELECT DATETIME,NODE,COL1 AA, ((COL2+COL3)/COL1) BB
    FROM                                                       
    TABLE
    WHERE DATETIME BETWEEN TRUNC(SYSDATE)-3 and TRUNC(SYSDATE)-2-1/86400 AND NODE='ABC')
    WHERE COL1 in                       
    (SELECT MAX(COL1) 
    FROM
    (SELECT DATETIME, COL1
    FROM TABLE
    WHERE DATETIME BETWEEN TRUNC(SYSDATE)-3 and TRUNC(SYSDATE)-2-1/86400 AND NODE='ABC'))
    The above gives one row in the form:

    Code:
    DATETIME NODE AA BB
    This query doesn't look very efficient to me. Moreover, I can only run the query for one day.

    Is there a way I can get the above-mentioned for more than 1 day? I need to query/compute values based on the maximum value of COL1 out of 24 hourly values for each day.

    Regards
    Shajju

  2. #2
    Join Date
    Oct 2002
    Location
    Cape Town, South Africa
    Posts
    253
    Essentially yes, that is the way you would have to do it. But you could make the query look a little simpler. Here is my re-write which should do exactly the same thing using the exact same resources:
    Code:
    SELECT DATETIME, NODE, COL1 AA, ((COL2+COL3)/COL1) BB
      FROM TABLE
     WHERE DATETIME >= TRUNC(SYSDATE)-3 
       AND DATETIME <  TRUNC(SYSDATE)-2 
       AND NODE='ABC'
       AND COL1 = (SELECT MAX(COL1) 
                     FROM TABLE
                    WHERE DATETIME >= TRUNC(SYSDATE)-3
                      AND DATETIME <  TRUNC(SYSDATE)-2 
                      AND NODE='ABC'))
    I would expect that you would have a two column index on TABLE(DATETIME, COL1) at a minimum. Obviously NODE is also an index candidate.

    Use explain plan to evaluate query cost.

Posting Permissions

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