Results 1 to 9 of 9
  1. #1
    Join Date
    Jul 2013
    Posts
    8

    Unanswered: How to get the top 3??

    I have a table with two columns: id and amount.
    Example:

    ID Amount
    A1 200
    B2 300
    C3 160
    D4 210
    E5 50
    F6 760
    G7 89
    G7 200
    D4 100

    Now I want to select the top-3, meaning the three id’s with the largest summation of amount. My result will then be:

    ID Amount
    B2 300
    D4 310
    F6 760

    How do I do that?

    Thanks

  2. #2
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Two examples.

    Example 2 showed less cost than Example 1.
    (Tested on DB2 Express-C 9.7.5 on Windows)

    Test data:
    Code:
    ------------------------------ Commands Entered ------------------------------
    WITH
     t( id , amount ) AS (
    VALUES
      ( 'A1' , 200 )
    , ( 'B2' , 300 )
    , ( 'C3' , 160 )
    , ( 'D4' , 210 )
    , ( 'E5' ,  50 )
    , ( 'F6' , 760 )
    , ( 'G7' ,  89 )
    , ( 'G7' , 200 )
    , ( 'D4' , 100 )
    )
    Example 1:
    Code:
    /*
    Total cost	0.06 timerons
    CPU cost	89,579.16 instructions
    I/O cost	0 I/Os
    First row cost	0.06 timerons
    Remote communication cost	0 timerons
    Remote total cost	0 timerons
    */
    SELECT id
         , amount
     FROM  (SELECT id
                 , SUM(amount) AS amount
             FROM  t
             GROUP BY
                   id
             ORDER BY
                   amount DESC
             FETCH FIRST 3 ROWS ONLY
           )
     ORDER BY
           amount ASC
    ;
    ------------------------------------------------------------------------------
    
    ID AMOUNT     
    -- -----------
    B2         300
    D4         310
    F6         760
    
      3 record(s) selected.

    Example 2:
    Code:
    /*
    Total cost	0.04 timerons
    CPU cost	57,931.21 instructions
    I/O cost	0 I/Os
    First row cost	0.03 timerons
    Remote communication cost	0 timerons
    Remote total cost	0 timerons
    */
    SELECT id
         , amount
     FROM  (SELECT id
                 , SUM(amount) AS amount
                 , ROW_NUMBER()
                      OVER(ORDER BY SUM(amount) ASC) AS rn
                 , COUNT(*)
                      OVER() AS cnt
             FROM  t
             GROUP BY
                   id
           )
     WHERE rn > cnt - 3
    ;
    ------------------------------------------------------------------------------
    
    ID AMOUNT     
    -- -----------
    B2         300
    D4         310
    F6         760
    
      3 record(s) selected.

  3. #3
    Join Date
    Jul 2013
    Posts
    8
    thnx very much

  4. #4
    Join Date
    Mar 2003
    Posts
    280
    Quote Originally Posted by tonkuma View Post
    Two examples.

    Example 2 showed less cost than Example 1.
    (Tested on DB2 Express-C 9.7.5 on Windows)

    Test data:
    Code:
    ------------------------------ Commands Entered ------------------------------
    WITH
     t( id , amount ) AS (
    VALUES
      ( 'A1' , 200 )
    , ( 'B2' , 300 )
    , ( 'C3' , 160 )
    , ( 'D4' , 210 )
    , ( 'E5' ,  50 )
    , ( 'F6' , 760 )
    , ( 'G7' ,  89 )
    , ( 'G7' , 200 )
    , ( 'D4' , 100 )
    )
    Example 1:
    Code:
    /*
    Total cost	0.06 timerons
    CPU cost	89,579.16 instructions
    I/O cost	0 I/Os
    First row cost	0.06 timerons
    Remote communication cost	0 timerons
    Remote total cost	0 timerons
    */
    SELECT id
         , amount
     FROM  (SELECT id
                 , SUM(amount) AS amount
             FROM  t
             GROUP BY
                   id
             ORDER BY
                   amount DESC
             FETCH FIRST 3 ROWS ONLY
           )
     ORDER BY
           amount ASC
    ;
    ------------------------------------------------------------------------------
    
    ID AMOUNT     
    -- -----------
    B2         300
    D4         310
    F6         760
    
      3 record(s) selected.

    Example 2:
    Code:
    /*
    Total cost	0.04 timerons
    CPU cost	57,931.21 instructions
    I/O cost	0 I/Os
    First row cost	0.03 timerons
    Remote communication cost	0 timerons
    Remote total cost	0 timerons
    */
    SELECT id
         , amount
     FROM  (SELECT id
                 , SUM(amount) AS amount
                 , ROW_NUMBER()
                      OVER(ORDER BY SUM(amount) ASC) AS rn
                 , COUNT(*)
                      OVER() AS cnt
             FROM  t
             GROUP BY
                   id
           )
     WHERE rn > cnt - 3
    ;
    ------------------------------------------------------------------------------
    
    ID AMOUNT     
    -- -----------
    B2         300
    D4         310
    F6         760
    
      3 record(s) selected.
    Example 2 can be slightly simplified:

    Code:
    ) SELECT id
         , amount
     FROM  (SELECT id
                 , SUM(amount) AS amount
                 , ROW_NUMBER()
                      OVER(ORDER BY SUM(amount) desc) AS rn
             FROM  t
             GROUP BY
                   id
           )
     WHERE rn <= 3 order by rn desc
    --
    Lennart

  5. #5
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Lennart,

    Fine!
    Yours was not only simpler but also less cost(with same test data and environment) than mine.

    Here is my test result.
    Code:
    /*
    Total cost	0.03 timerons
    CPU cost	48,602.84 instructions
    I/O cost	0 I/Os
    First row cost	0.03 timerons
    Remote communication cost	0 timerons
    Remote total cost	0 timerons
    */
    SELECT id
         , amount
     FROM  (SELECT id
                 , SUM(amount) AS amount
                 , ROW_NUMBER()
                      OVER(ORDER BY SUM(amount) DESC) AS rn
             FROM  t
             GROUP BY
                   id
           )
     WHERE rn <= 3
     ORDER BY
           rn DESC
    ;
    ------------------------------------------------------------------------------
    
    ID AMOUNT     
    -- -----------
    B2         300
    D4         310
    F6         760
    
      3 record(s) selected.

  6. #6
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Summary of cost.

    Code:
    +-------------------------------+---------------+------------------------+
    | Query Example                 | Total cost    | CPU cost               |
    +-------------------------------+---------------+------------------------+
    | FETCH FIRST ...  (Example 1)  | 0.06 timerons | 89,579.16 instructions |
    | COUNT(*) OVER()  (Example 2)  | 0.04 timerons | 57,931.21 instructions |
    | ORDER BY rn DESC (by Lennart) | 0.03 timerons | 48,602.84 instructions |
    +-------------------------------+---------------+------------------------+

  7. #7
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Here is a little complicated but a slightly less cost example.
    (I don't know that slight difference of costs have some practical meaning. But, I tried to reduce the cost, mainly for my fun.)

    Example 3:
    Code:
    /*
    Total cost	0.03 timerons
    CPU cost	48,230.23 instructions
    I/O cost	0 I/Os
    First row cost	0.03 timerons
    Remote communication cost	0 timerons
    Remote total cost	0 timerons
    */
    SELECT id , amount
     FROM  (SELECT id , amount
                 , ROW_NUMBER()
                      OVER(ORDER BY amount DESC) AS rn             
             FROM  (SELECT id
                         , SUM(amount)
                              OVER(PARTITION BY id) AS amount
                         , RANK()
                              OVER(PARTITION BY id
                                       ORDER BY amount) AS rank_amount
                     FROM  t
                   )
             WHERE rank_amount = 1
           )
     WHERE rn <= 3
     ORDER BY
           rn DESC
    ;
    ------------------------------------------------------------------------------
    
    ID AMOUNT     
    -- -----------
    B2         300
    D4         310
    F6         760
    
      3 record(s) selected.

    Summary of cost.
    Code:
    +-------------------------------+---------------+------------------------+
    | Query Example                 | Total cost    | CPU cost               |
    +-------------------------------+---------------+------------------------+
    | FETCH FIRST ...  (Example 1)  | 0.06 timerons | 89,579.16 instructions |
    | COUNT(*) OVER()  (Example 2)  | 0.04 timerons | 57,931.21 instructions |
    | ORDER BY rn DESC (by Lennart) | 0.03 timerons | 48,602.84 instructions |
    | RANK() OVER(...) (Example 3)  | 0.03 timerons | 48,230.23 instructions |
    +-------------------------------+---------------+------------------------+

  8. #8
    Join Date
    Mar 2003
    Posts
    280
    Quote Originally Posted by tonkuma View Post
    Here is a little complicated but a slightly less cost example.
    (I don't know that slight difference of costs have some practical meaning. But, I tried to reduce the cost, mainly for my fun.)
    [...]
    Summary of cost.
    Code:
    +-------------------------------+---------------+------------------------+
    | Query Example                 | Total cost    | CPU cost               |
    +-------------------------------+---------------+------------------------+
    | FETCH FIRST ...  (Example 1)  | 0.06 timerons | 89,579.16 instructions |
    | COUNT(*) OVER()  (Example 2)  | 0.04 timerons | 57,931.21 instructions |
    | ORDER BY rn DESC (by Lennart) | 0.03 timerons | 48,602.84 instructions |
    | RANK() OVER(...) (Example 3)  | 0.03 timerons | 48,230.23 instructions |
    +-------------------------------+---------------+------------------------+
    That's interesting, I would have guessed that the extra nesting and olap functions would have caused additional cost, but looking at the plan they appear to be very similar, but the last example is a teeny wheeny cheaper. Example 2:

    Code:
            Total Cost:             0.00746664
            Query Degree:           1
    
           Rows 
          RETURN
          (   1)
           Cost 
            I/O 
            |
             3 
          TBSCAN
          (   2)
        0.00746664 
             0 
            |
             3 
          SORT  
          (   3)
        0.00711556 
             0 
            |
             3 
          TBSCAN
          (   4)
        0.00551156 
             0 
            |
             3 
          SORT  
          (   5)
        0.00516048 
             0 
            |
             9 
          GRPBY 
          (   6)
        0.00324649 
             0 
            |
             9 
          TBSCAN
          (   7)
        0.00285718 
             0 
            |
             9 
          SORT  
          (   8)
        0.00227583 
             0 
            |
             9 
          TBSCAN
          (   9)
        5.52643e-05 
             0 
            |
             9 
     TABFNC: SYSIBM  
          GENROW
            Q1

    Last example:

    Code:
            Total Cost:             0.00740391
            Query Degree:           1
    
           Rows 
          RETURN
          (   1)
           Cost 
            I/O 
            |
           0.36 
          TBSCAN
          (   2)
        0.00740391 
             0 
            |
           0.36 
          SORT  
          (   3)
        0.00715415 
             0 
            |
           0.36 
          TBSCAN
          (   4)
        0.00603036 
             0 
            |
           0.36 
          SORT  
          (   5)
         0.0057806 
             0 
            |
           0.36 
          FILTER
          (   6)
        0.00534572 
             0 
            |
             9 
          TBSCAN
          (   7)
        0.00311995 
             0 
            |
             9 
          SORT  
          (   8)
        0.00253861 
             0 
            |
             9 
          TBSCAN
          (   9)
        5.52643e-05 
             0 
            |
             9 
     TABFNC: SYSIBM  
          GENROW
            Q1
    For the fun of it
    --
    Lennart

  9. #9
    Join Date
    Nov 2011
    Posts
    334
    hi,
    I think maybe db2 optimizer underestimate the cost of last example.
    I notice the filter step:
    0.36
    FILTER
    ( 6)
    0.00534572

    the estimated card 0.36 is not correct obviously.

Posting Permissions

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