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

    Unanswered: highest 3 values

    Hi

    Can anyone help me with the syntax to select the highest 3 values from a column which already is created using an equation. e.g.,

    Code:
    SELECT  trunc(datetime,'hh'),msc, round(DECODE(sum(NSCAN),0,0,max(ACCLOAD)/max(NSCAN)),2) CP
    
    FROM SCHEMA.TABLE
    WHERE  a.DATETIME between trunc(sysdate,'hh')-1/24 and trunc(sysdate,'hh')-1/24/3600
    AND ((MSC = 'OMHLR3') OR (MSC = 'OMMSC10') OR (MSC = 'OMMSC3') OR (MSC = 'OMMSC4') OR (MSC = 'OMMSC7') OR (MSC = 'OMMSC8') OR (MSC = 'OMMSC9'))
    
    group by trunc(datetime,'hh'), msc
    order by cp desc

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >Can anyone help me with the syntax to select the highest 3 values from a column which already is created using an equation.

    This is a FAQ

    Post Operating System (OS) name & version for DB server system.
    Post results of SELECT * from v$version.

    Code:
    SELECT   Trunc(datetime,'hh'), 
             msc, 
             Round(Decode(Sum(nscan),0,0, 
                                     Max(accload) / Max(nscan)), 
                   2) cp 
    FROM     SCHEMA.table 
    WHERE    a.datetime BETWEEN Trunc(SYSDATE,'hh') - 1 / 24 AND Trunc(SYSDATE,'hh') - 1 / 24 / 3600 
             AND ((msc = 'OMHLR3') 
                   OR (msc = 'OMMSC10') 
                   OR (msc = 'OMMSC3') 
                   OR (msc = 'OMMSC4') 
                   OR (msc = 'OMMSC7') 
                   OR (msc = 'OMMSC8') 
                   OR (msc = 'OMMSC9')) 
    GROUP BY Trunc(datetime,'hh'), 
             msc 
    ORDER BY cp DESC
    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

    v$version

    Oracle9i Enterprise Edition Release 9.2.0.7.0 - 64bit Production
    PL/SQL Release 9.2.0.7.0 - Production
    CORE 9.2.0.7.0 Production
    TNS for HPUX: Version 9.2.0.7.0 - Production
    NLSRTL Version 9.2.0.7.0 - Production

  4. #4
    Join Date
    Aug 2008
    Posts
    464
    I would like to explain my concern further. Basically I'm trying to select the top 3 values (highest 3 CP_USAGE) from 2 tables.

    TABLE 1

    Code:
    SELECT  trunc(datetime,'hh'),msc, round(DECODE(sum(NSCAN),0,0,max(ACCLOAD)/max(NSCAN)),2) CP_USAGE
    
    FROM SCHEMA.TABLE A
    
    WHERE  a.DATETIME between trunc(sysdate,'hh')-1/24 and trunc(sysdate,'hh')-1/24/3600 
    AND ((MSC = 'OMHLR3') OR (MSC = 'OMMSC10') OR (MSC = 'OMMSC3') OR (MSC = 'OMMSC4') OR (MSC = 'OMMSC7') OR (MSC = 'OMMSC8') OR (MSC = 'OMMSC9'))
    
    group by trunc(datetime,'hh'), msc
    order by cp_usage desc
    OUTPUT

    DATETIME MSC CP_USAGE
    09/06/2009 12:00:00 OMMSC4 31.27
    09/06/2009 12:00:00 OMMSC10 30.76
    09/06/2009 12:00:00 OMMSC3 30.34
    09/06/2009 12:00:00 OMMSC7 28.85
    09/06/2009 12:00:00 OMMSC8 23.94
    09/06/2009 12:00:00 OMMSC9 18.82


    TABLE 2

    Code:
    SELECT C.MSC,
    TRUNC(C.DATETIME,'HH') DAY,
    TO_CHAR(MIN(C.DATETIME),'HH24:MI')||'-'||TO_CHAR(MAX(C.DATETIME)+(1/1440*15),'HH24:MI') TIME_INTERVAL,
    ROUND(AVG(NVL(ct_bapm,0)+NVL(ct_baps,0)+NVL(ct_cap0,0)+NVL(ct_cap1,0)+NVL(ct_cap2,0)+NVL(ct_cap3,0)+NVL(ct_cap4,0))  / 6000 * 100,3) CP_USAGE                
    FROM 
    SCHEMA.TABLE C
    WHERE c.DATETIME between trunc(sysdate,'hh')-1/24 and trunc(sysdate,'hh')-1/24/3600 
    AND ((C.MSC = 'MSC1') OR (C.MSC = 'MSC11') OR (C.MSC = 'MSC12') OR (C.MSC = 'MSC13') OR (C.MSC = 'MSC2') OR (C.MSC = 'MSC6')) 
    GROUP BY C.MSC , TRUNC(C.DATETIME,'HH')
    ORDER BY CP_USAGE DESC
    OUTPUT

    MSC DAY TIME_INTERVAL CP_USAGE
    MSC1 09/06/2009 12:00:00 12:00-12:30 42.52
    MSC6 09/06/2009 12:00:00 12:00-12:30 36.07
    MSC11 09/06/2009 12:00:00 12:00-12:30 31.82
    MSC13 09/06/2009 12:00:00 12:00-12:30 29.15
    MSC2 09/06/2009 12:00:00 12:00-12:30 24.75
    MSC12 09/06/2009 12:00:00 12:00-12:30 19.74

  5. #5
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    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.

  6. #6
    Join Date
    Aug 2008
    Posts
    464

    Thanks

    I used the dense rank and it did the job for me. Even when I used rank, I got the same result.

Posting Permissions

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