Results 1 to 5 of 5

Thread: max value

  1. #1
    Join Date
    Aug 2008
    Posts
    464

    Unanswered: max value

    Hi

    Apologies for another naive question.

    I have a query that returns 24 rows.

    Code:
    select to_char(datetime,'HH24:MI') TIME, max(counter) max_counter from schema.table where datetime between trunc(sysdate)-1 and trunc(sysdate)
    group by datetime
    I want to select the max out of these 24 values and the corresponding time but when I group by datetime it gives me 24 values for each of the 24 hours.

    Regards
    Shajju
    Last edited by shajju; 10-31-09 at 09:51.

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    If you want only the MAX value, then MAX() needs to be used in WHERE clause
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Aug 2008
    Posts
    464
    Thanks but I want the max value out of the 24 hourly values and the corresponding datetime.

    I tried using MAX in the Where clause:

    select * from
    Code:
    (select to_char(datetime,'HH24:MI') TIME, max(cpupeak) CP_LOAD from schema.table where datetime between trunc(sysdate)-1 and trunc(sysdate)
    group by datetime)
    where CP_LOAD=MAX(CP_LOAD)
    but it said, Group function is not allowed here.

  4. #4
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    Quote Originally Posted by anacedent View Post
    If you want only the MAX value, then MAX() needs to be used in WHERE clause
    You can't have an aggregate in the WHERE clause, this needs to be put into the HAVING clause.

    But in this case you'll need a sub-select anyway.

    Code:
    SELECT to_char(datetime,'HH24:MI'), 
           cpupeak
    FROM the_table
    WHERE datetime BETWEEN trunc(sysdate)-1 AND trunc(sysdate)
    AND cpupeak = (SELECT max(cpupeak) FROM the_table)

  5. #5
    Join Date
    Aug 2008
    Posts
    464
    Hey, thanks Shammat and everyone. Truly Appreciated.

Posting Permissions

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