Results 1 to 3 of 3
  1. #1
    Join Date
    Mar 2004
    Location
    India
    Posts
    41

    Unanswered: Need to optimize the SQL Query.

    Hi Everyb,
    I need to optimize the below query.the following query returns only 632 out 4460000 rows.

    Datatypes:

    Region varchar
    Status int
    Server varchar
    Directory varchar
    PercentUsed decimal
    AvailableSpace int
    scantime datetime



    select Max(status),server,max(scantime)
    from Tbl_LotusServerDiskSpaceReport b
    where
    scantime=(select max(scantime)
    from Tbl_LotusServerDiskSpaceReport a
    where
    a.server=b.server
    and
    a.percentused is not null
    and
    a.AvailableSpace is not null)
    and b.percentused is not null
    and b.AvailableSpace
    is not null
    group by server order by server


    Thanks in Advance ,
    Arun

  2. #2
    Join Date
    Mar 2002
    Location
    Reading, UK
    Posts
    1,137

    Post

    depending on what you want exactly you could do

    select Max(status),server,max(scantime)
    from Tbl_LotusServerDiskSpaceReport b
    where and b.percentused is not null
    and b.AvailableSpace is not null
    group by server order by server

    or

    select server,Max(status),server,max(scantime)
    (
    select server,
    FIRST_VALUE(status) OVER (PARTITION BY server ORDER BY scantime DESC ) AS status,
    FIRST_VALUE(scantime) OVER (PARTITION BY server ORDER BY scantime DESC ) AS scantime
    from Tbl_LotusServerDiskSpaceReport b
    where and b.percentused is not null
    and b.AvailableSpace is not null
    )
    group by server order by server

    Alan

  3. #3
    Join Date
    May 2004
    Location
    Dominican Republic
    Posts
    721
    So, if I get the question correctly, I assume: "Per server, get me the highest SCANTIME and out of that SCANTIME, get me the higher STATUS."

    If so, here are two tricks I use a lot for this type of query to avoid a correlated subquery (which is what you used).

    The first one, using a MAX trick:

    Code:
    select server, max(scantime), mod(max(to_number(to_char(scantime,'yyyymmdd')) * 100 + status),100) status
      from t
     where percentused is not null and avaliablespace is not null
     group by server
    The other one, with the use of MAX aggregate with the KEEP clause.
    Code:
    select server, max(scantime), max(status) keep (dense_rank last order by scantime) status
      from t
     where percentused is not null and avaliablespace is not null
     group by server

Posting Permissions

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