Results 1 to 2 of 2

Thread: Query Tuning...

  1. #1
    Join Date
    May 2004
    Posts
    3

    Unanswered: Query Tuning...

    Hi,

    I have the following query and it needs to obtain the minimum value, the time it occurred, the maximum value and the time it occured... however for simplicitys sake, I've only included one and figure I can apply the concept to the whole query. Is there a way to get both values without having run the original query as a subquery to get the time it occured?

    Thank you!

    select sum_data.tagname, sum_data.data_date, sum_data.mtd_min, sum_data.mtd_mintime
    from
    (select b.tagname, b.data_date,
    (select min(a.value) as mtd_min
    from sh_hourstats_2002 a
    where a.tagname = b.tagname
    and a.data_date <= b.data_date
    and to_char(a.data_date,'MON-RRRR') = to_char(b.data_date, 'MON-RRRR')
    and a.data_type = b.data_type) as mtd_min,
    (select min(data_date) as mtd_mintime
    from sh_hourstats_2002 a
    where a.tagname = b.tagname
    and a.data_date <= b.data_date
    and to_char(a.data_date,'MON-RRRR') = to_char(b.data_date, 'MON-RRRR')
    and a.data_type = b.data_type
    and a.value = (select min(a.value) as mtd_min
    from sh_hourstats_2002 a
    where a.tagname = b.tagname
    and a.data_date <= b.data_date
    and to_char(a.data_date,'MON-RRRR') = to_char(b.data_date, 'MON-RRRR')
    and a.data_type = b.data_type)) as mtd_mintime
    from sh_hourstats_2002 b) sum_data

  2. #2
    Join Date
    Jan 2004
    Location
    Scottsdale, AZ
    Posts
    106
    Review the use of analytic functions:
    http://download-west.oracle.com/docs...tion.htm#81409
    "Take Control!
    Make The Choice!
    Accept The Consequences!
    Deal With The Results! "

Posting Permissions

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