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

    Unanswered: Selecting the Max for more than one day - 10g

    Hi

    I Need some help with Oracle SQL.

    I have a query like the one below which gives me one value for 1 day:

    Code:
    SELECT DATETIME ,NODE, MAX(AA) 
    FROM (SELECT DATETIME, NODE, A+B AA FROM SCHEMA.HOURLY_TABLE
    WHERE
    DATETIME BETWEEN TRUNC(SYSDATE)-1 AND TRUNC(SYSDATE)-1/86400
    AND NODE=1)
    GROUP BY DATETIME, NODE
    Could anyone advise how I can run the above query for 1 month?

    Regards
    Shajju

  2. #2
    Join Date
    Dec 2007
    Posts
    253
    where datetime between trunc(sysdate, 'Month') and add_months(trunc(sysdate,'Month'), 1)

  3. #3
    Join Date
    Aug 2008
    Posts
    464
    Thanks pablolee. You wouldn't be so kind as to help me understand this where clause?

  4. #4
    Join Date
    Aug 2008
    Posts
    464
    I take it this WHERE clause takes care of the calender month.

    Please tell me how I can cover the last 30 days?

  5. #5
    Join Date
    Dec 2007
    Posts
    253
    Quote Originally Posted by shajju View Post
    I take it this WHERE clause takes care of the calender month.
    Did you try it? If you did, what happened? Can I assume that you are not familiar with the extended syntax of the trunc function, nor the add_months function?
    You can (and really, really should) see all the info you need on these two by looking up the documentation and starting here:

    Please tell me how I can cover the last 30 days?
    You managed to get it, on your own, for one day. Simply extend that logic for 30 days.

  6. #6
    Join Date
    Aug 2008
    Posts
    464
    Thanks. Will do.
    Last edited by shajju; 10-06-13 at 06:15.

Posting Permissions

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