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

    Unanswered: Extracting rows based on the max value of a column

    Hi guys

    Using 9i.

    BBB is type 'VARCHAR2' and all COLumns are type 'number'.

    I'm trying to write a query to extract a row based on the maximum value of a column. The complication is that the result is being grouped by BBB (which are 8 in number like BBB1, BBB2...BBB8) and I can't figure out how to also display the corresponding values of 'B' and 'C' for the selected rows.

    If I group by 'B' and 'C', the result is not filtered for MAX(A) and I can't just select 'B' and 'C' along with MAX(A) without using some function like (MAX...etc) which is what I don't want to do because I want the corresponding values for 'B' and 'C'.

    Any advice will be greatly appreciated.

    Code:
    (Select BBB ,TRUNC(DATETIME), MAX(A) 
    
    FROM 
    
    (SELECT
    
     BBB, DATETIME, (Sum(COL1)/AVG(COL2)) as A,
     
     Sum(COL3)/SUM(COL4)*100 as B,
     
     sum(COL5)/sum(COL6)*100 as C
    
    FROM
    SCHEMA.TABLE
    WHERE
      DATETIME BETWEEN TO_DATE('13-02-10 00:00', 'dd-mm-yy hh24:mi') AND TO_DATE('13-02-10 23:59', 'dd-mm-yy hh24:mi') 
      
    GROUP BY BBB,DATETIME) HRLY_TABLE GROUP BY BBB,TRUNC(DATETIME))

  2. #2
    Join Date
    Aug 2008
    Posts
    464
    I just wanted to add that:

    I managed to get the desired result but using probably a very poor way of writing SQL (i.e., I used UNION ALL and repeated the below SQL for all 8 BBBs) Is there a smarter way?:

    select * from(

    SELECT

    BBB,DATETIME, Sum(COL1)/AVG(COL2) as A,

    Sum(COL3)/SUM(COL4)*100 as B
    ,
    sum(COL5)/sum(COL6)*100 as C

    FROM
    SCHEMA.TABLE
    WHERE
    DATETIME BETWEEN TO_DATE('13-02-10 00:00', 'dd-mm-yy hh24:mi') AND TO_DATE('13-02-10 23:59', 'dd-mm-yy hh24:mi') and BBB='BBB1'

    GROUP by BBB,datetime)

    where A = (select MAX(A) from

    (SELECT

    BBB,DATETIME,Sum(COL1)/AVG(COL2) as A
    ,
    Sum(COL3)/Sum(COL4)*100 as B,

    sum(COL5)/sum(COL6)*100 as C

    FROM
    SCHEMA.TABLE
    WHERE
    DATETIME BETWEEN TO_DATE('13-02-10 00:00', 'dd-mm-yy hh24:mi') AND TO_DATE('13-02-10 23:59', 'dd-mm-yy hh24:mi') and BBB='BBB1'

    GROUP by BBB,datetime))

    Regards

  3. #3
    Join Date
    Jul 2003
    Posts
    2,296
    read up on oracle analytics.
    I have a feeling this will solve all of your issues.
    - The_Duck
    you can lead someone to something but they will never learn anything ...

Posting Permissions

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