Results 1 to 5 of 5
  1. #1
    Join Date
    Sep 2008
    Posts
    34

    Unanswered: Fetch LAST 10 Rows only?

    I want to show the top 10 and bottom 10 rows in a table of a 100 row table and display it like so...

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    91
    92
    93
    94
    95
    96
    97
    98
    99
    100


    I am able to get the asecending first 10 without problems. I am able to get the last ten rows (by sortin DESCENDINGLY) and retrieving the first 10 rows only, but then the data looks like so...

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    100
    99
    98
    97
    96
    95
    94
    93
    92
    91

    Is there a FETCH LAST XX ROWS only? or another way to accomplish my desired result? thanks!

  2. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    Try this:

    with t1 (code,name) as (select code,name from mytable order by code fetch first 10 rows only), t2 (code,name) as (select code,name from mytable order by code desc fetch first 10 rows only) select * from t1 union all select * from t2 order by code

    Andy

  3. #3
    Join Date
    Sep 2008
    Posts
    34
    Dude, you rock! I have not seen the WITH command/function before. Is that how you create a temporary table?

  4. #4
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    That's a "common table expression" and it is standard SQL. Very often (usually if you don't use recursion) you can express this as a subselect as well.
    Code:
    SELECT number
    FROM   ( SELECT number
             FROM   t
             ORDER BY number ASC
             FETCH FIRST 10 ROWS ONLY ) AS t1
    UNION ALL
    SELECT number
    FROM   ( SELECT number
             FROM   t
             ORDER BY number DESC
             FETCH FIRST 10 ROWS ONLY ) AS t2
    ORDER BY number
    
    NUMBER
    -----------
              1
              2
              3
              4
              5
              6
              7
              8
              9
             10
             91
             92
             93
             94
             95
             96
             97
             98
             99
            100
    
      20 record(s) selected.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  5. #5
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    If max number was known.
    Code:
    WITH
    /* Generate TestData */
     TestData(n) AS (
    VALUES 1
    /**/ UNION ALL /**/
    SELECT n + 1
      FROM TestData
     WHERE n < 100
    )
    /* End of TestData */
    SELECT n
      FROM (SELECT n
                 , ROW_NUMBER() OVER(ORDER BY n) rn
              FROM TestData
           ) S
     WHERE rn <= 10
       OR  rn >  90
     ORDER BY n
    ;
    If max number was not known.
    Code:
    WITH
    /* Generate TestData */
     TestData(n) AS (
    VALUES 1
    /**/ UNION ALL /**/
    SELECT n + 1
      FROM TestData
     WHERE n < 100
    )
    /* End of TestData */
    SELECT n
      FROM (SELECT n
                 , ROW_NUMBER() OVER(ORDER BY n) rn
              FROM TestData
           ) S
         , (SELECT MAX(n) AS max_n
              FROM TestData
           ) T
             
     WHERE rn <= 10
       OR  rn >  max_n - 10
     ORDER BY n
    ;

Posting Permissions

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