Fetch LAST 10 Rows only?

curtmorehouse · #1 (**permalink**) 12-04-08, 15:05

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!

ARWinner · #2 (**permalink**) 12-04-08, 15:20

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

curtmorehouse · #3 (**permalink**) 12-04-08, 20:59

Dude, you rock! I have not seen the WITH command/function before. Is that how you create a temporary table?

stolze · #4 (**permalink**) 12-05-08, 04:40

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.

tonkuma · #5 (**permalink**) 12-05-08, 06:06

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
;

Internet Services

Web Development

Digital Marketing

Consumer Tech