Db2

Db2

Connect with Db2, open source, and other data experts to gain value from your data, share insights, and solve problems.

 View Only

  • 1.  sql question

    Posted Tue July 14, 2020 08:06 AM
    I am struggling with an sql and need some ideas...
    I have a table with hostname,dbname,table,size,date for all tables by db/host
    I calculate the increase size of tables compared to data from previous month
    so far no problem
    but I would like to get only the top 10 growing tables by host/db
    using fetch first 10 rows would return only top 10 from any db or order sequence
    is there a possibility to apply some filter on the result of group by - similar to having.. but fetching only top 10 by group
    host1,db1,table1,inc_size
    host1,db1,table2,inc_size
    ..   8 more
    host2,db2,table1,inc_size
    ...
    if you have any idea or hint, already thanks for all update

    ------------------------------
    Thanks for all answers
    Best Regards,
    Guy Przytula
    ------------------------------

    #Db2


  • 2.  RE: sql question

    Posted Tue July 14, 2020 08:32 AM
    Guy,

    I think this will do what you want, sign your host,db,table,inc_size as the input table.

    I've used an OLAP expression with the RANK() function to give numbers to the top 10 values in the group you want.   I've done this in a CTE and have then filtered out only the first 10 of these.  The only time it would give more rows if there were actually more than 10 rows for a database all with the same inc_size, but I guess that is unlikely to happen..

    with ranked (host,db,table,rankit,inc_size) as
    (
     SELECT
     host,
     db,
     table,
     rank() over(partition by host,db1 order by inc_size desc),
     inc_size
     FROM
     TESTIT
    )
    SELECT
    host,db,table,inc_size
    from
    ranked
    where rankit <= 10
    order by
    host,db,rankit
    ;



    Original Message


  • 3.  RE: sql question

    Posted Tue July 14, 2020 09:44 AM
    Edited by System Admin Fri January 20, 2023 04:42 PM
    many thanks for the prompt answer..   this will do....
    Tested the query and the result is exactly what I needed

    ------------------------------
    Thanks for all answers
    Best Regards,
    Guy Przytula
    #Db2


Related Content