Results 1 to 3 of 3

Thread: top ten values

  1. #1
    Join Date
    Aug 2006
    Posts
    3

    Unanswered: top ten values

    I am a new bee in Db2, Can someone tell me how to get 10 max values from a table in Db2, earlier i was using oracle where there was rownum but in db2 w do not have this..

    Thanks
    Deep

  2. #2
    Join Date
    Mar 2004
    Posts
    46
    I suppose you want the top 10 rows from a table, right ?
    Then you can use "fetch first 10 rows only" clause at the end of your SQL.

    However note that the "first 10 rows" depends on the access path that the query takes (unless you have an order by clause or something that forces the same order of rows).

  3. #3
    Join Date
    Sep 2004
    Location
    Belgium
    Posts
    1,126
    Quote Originally Posted by ps_deep
    how to get 10 max values from a table in Db2
    For the 10 max values of column k of table t:
    Code:
    SELECT DISTINCT k FROM t
    ORDER BY k DESC
    FETCH FIRST 10 ROWS ONLY
    OPTIMIZE FOR 1 ROW
    The "standard SQL" way to obtain this would be:
    Code:
    SELECT t1.k FROM t AS t1, t AS t2
    WHERE t1.k <= t2.k
    GROUP BY t1.k
    HAVING count(*) <= 10
    Last edited by Peter.Vanroose; 08-20-06 at 15:16.
    --_Peter Vanroose,
    __IBM Certified Database Administrator, DB2 9 for z/OS
    __IBM Certified Application Developer
    __ABIS Training and Consulting
    __http://www.abis.be/

Posting Permissions

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