Results 1 to 7 of 7

Thread: SQL query -10g

  1. #1
    Join Date
    Aug 2008
    Posts
    464

    Unanswered: SQL query -10g

    Hi

    Kind of confused. Will be grateful for some advice.

    The following query:

    Code:
     SELECT DATETIME,HOST, SUM(ACOUNT) AAA 
    FROM TABLE_HR
    WHERE DATETIME BETWEEN TRUNC(SYSDATE)-1 AND TRUNC(SYSDATE)-1/3600 GROUP BY DATETIME,HOST
    gives 48 rows

    Code:
    DATETIME	              HOST    AAA
    
    31/12/2012             	E1	230499
    31/12/2012         	E2	211469
    31/12/2012 01:00:00	E1	219756
    31/12/2012 01:00:00	E2	204182
    31/12/2012 02:00:00	E1	217593
    31/12/2012 02:00:00	E2	201187
    31/12/2012 03:00:00	E1	212928
    31/12/2012 03:00:00	E2	197264
    31/12/2012 04:00:00	E1	210225
    31/12/2012 04:00:00	E2	192727
    31/12/2012 05:00:00	E1	211562
    31/12/2012 05:00:00	E2	194335
    31/12/2012 06:00:00	E1	222106
    31/12/2012 06:00:00	E2	203339
    31/12/2012 07:00:00	E1	232623
    31/12/2012 07:00:00	E2	211227
    31/12/2012 08:00:00	E1	239226
    31/12/2012 08:00:00	E2	215714
    31/12/2012 09:00:00	E1	245962
    31/12/2012 09:00:00	E2	219992
    31/12/2012 10:00:00	E1	250318
    31/12/2012 10:00:00	E2	223295
    31/12/2012 11:00:00	E1	252705
    31/12/2012 11:00:00	E2	225291
    31/12/2012 12:00:00	E1	253795
    31/12/2012 12:00:00	E2	226195
    31/12/2012 13:00:00	E1	257145
    31/12/2012 13:00:00	E2	227181
    31/12/2012 14:00:00	E1	268069
    31/12/2012 14:00:00	E2	225367
    31/12/2012 15:00:00	E1	264638
    31/12/2012 15:00:00	E2	221729
    31/12/2012 16:00:00	E1	261403
    31/12/2012 16:00:00	E2	223675
    31/12/2012 17:00:00	E1	266710
    31/12/2012 17:00:00	E2	225106
    31/12/2012 18:00:00	E1	276549
    31/12/2012 18:00:00	E2	226868
    31/12/2012 19:00:00	E1	279629
    31/12/2012 19:00:00	E2	226451
    31/12/2012 20:00:00	E1	280168
    31/12/2012 20:00:00	E2	226178
    31/12/2012 21:00:00	E1	278794
    31/12/2012 21:00:00	E2	225743
    31/12/2012 22:00:00	E1	276715
    31/12/2012 22:00:00	E2	223291
    31/12/2012 23:00:00	E1	272515
    31/12/2012 23:00:00	E2	219976
    Q:
    How can I select the two rows where AAA is max for E1 and for E2?

    Regards
    Shajju

  2. #2
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Here's one option (untested, though - I don't have your tables):
    Code:
    with your_query as
      (select datetime, host, sum(account) aaa
       from table_hr
       where datetime between trunc(sysdate) - 1
                          and trunc(sysdate) - 1 / 3600
       group by datetime, host
      ),
       max_aaa as
      (select host, max(aaa) max_aaa_per_host
       from your_query
       group by host
      )
    select q.datetime, m.host, m.max_aaa_per_host
    from max_aaa m,
         your_query q
    where q.host = m.host
      and q.aaa = m.max_aaa_per_host;

  3. #3
    Join Date
    Aug 2008
    Posts
    464
    OK, a bit incomplete....


    I can use

    Code:
    SELECT MAX(DATETIME), HOSTNAME, MAX(AAA) FROM 
    (
     (SELECT DATETIME,HOSTNAME, SUM(ACOUNT) AAA 
    FROM TABLE_HR
    WHERE DATETIME BETWEEN TRUNC(SYSDATE)-1 AND TRUNC(SYSDATE)-1/3600 GROUP BY DATETIME,HOSTNAME) 
    )
    WHERE DATETIME BETWEEN TRUNC(SYSDATE)-1 AND TRUNC(SYSDATE)-1/3600 GROUP BY HOSTNAME
    but I want to retrieve the corresponding datetime and not the max and can't group by datetime. Which aggregate function can I use to accommodate the actual datetime in an expression with a group by clause?

  4. #4
    Join Date
    Nov 2003
    Posts
    2,932
    Provided Answers: 12
    Something like:

    Code:
    SELECT datetime, 
           host,
           aaa
    FROM (
      SELECT datetime,
             host, 
             sum(account) as aaa,
             rank() over (partition by datetime, host order by sum(account) desc) as rnk
      FROM table_hr
      WHERE datetime BETWEEN TRUNC(SYSDATE)-1 AND TRUNC(SYSDATE)-1/3600 
      GROUP BY datetime, host
    )
    WHERE rnk = 1;
    I will not read nor answer questions where the SQL code is messy and not formatted properly using [code] tags: http://www.dbforums.com/misc.php?do=bbcode#code

    Tips for good questions:

    http://tkyte.blogspot.de/2005/06/how...questions.html
    http://wiki.postgresql.org/wiki/SlowQueryQuestions
    http://catb.org/esr/faqs/smart-questions.html

  5. #5
    Join Date
    Aug 2008
    Posts
    464
    Thanks but this is assigning RNK=1 to all the rows.

  6. #6
    Join Date
    Nov 2003
    Posts
    2,932
    Provided Answers: 12
    Quote Originally Posted by shajju View Post
    Thanks but this is assigning RNK=1 to all the rows.
    Then sum(acount) will return the same value as acount itselft and your group by isn't working the way you think. I guess there is something you are not telling us.

    Can you post an example on SQL Fiddle ?
    I will not read nor answer questions where the SQL code is messy and not formatted properly using [code] tags: http://www.dbforums.com/misc.php?do=bbcode#code

    Tips for good questions:

    http://tkyte.blogspot.de/2005/06/how...questions.html
    http://wiki.postgresql.org/wiki/SlowQueryQuestions
    http://catb.org/esr/faqs/smart-questions.html

  7. #7
    Join Date
    Aug 2008
    Posts
    464
    It worked!!

    It was a syntax problem.

    Many Thanks
    Shajju

Posting Permissions

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