Re: limiting rows returned in a sql select

#1

ceprnatwork (AT) hotmail (DOT) com (the dragon) wrote in message news:<6e59cc25.0308200651.6edb0e2 (AT) posting (DOT) google.com>...

Quote:

I am using db2 v7.x. Command center on my PC, odbc to a database on
the MF. I have a simple select, select * from ps_ledger_adb_mtd, and
I only want to see the first 10 rows, not all 5200 plus rows. I tried
using this, select * from ps_ledger_adb_mtd fetch first 10 rows only,
and got this error message: SQL0199N The use of the reserved word
"FETCH" following "" is not valid.

Expected tokens may include: "FOR WITH ORDER UNION EXCEPT QUERYNO
OPTIMIZE ".

SQLSTATE=42601

I dont know why "fetch first ...", doesnt work, but you could try something like:

select * from (
select x.*, rownumber() over () as rn from ps_ledger_adb_mtd x
) where rn < 10

this is not as compact as "fetch first ..." but is more powerfull since you can do:

select * from (
select x.*, rownumber() over () as rn from ps_ledger_adb_mtd x
) where rn between 10000 and 10010

[...]

HTH
/Lennart
--
the above email no longer works due to spam.
values'lennart'||CHR(46)||'jonsson'||CHR(64)||'enl ight'||CHR(46)||'net'

#2

here's what I get:

------------------------------ Command Entered ------------------------------
select * from (
select x.*, rownumber() over () as rn from ps_ledger_adb_mtd x
) where rn < 10
;
-----------------------------------------------------------------------------
SQL0104N An unexpected token "(" was found following "". Expected tokens may

include: ", FROM INTO ". SQLSTATE=42601

#3

ceprnatwork (AT) hotmail (DOT) com (the dragon) wrote in message news:<6e59cc25.0308251403.3d92bf33 (AT) posting (DOT) google.com>...

Quote:

here's what I get:

------------------------------ Command Entered ------------------------------
select * from (
select x.*, rownumber() over () as rn from ps_ledger_adb_mtd x
) where rn < 10
;
-----------------------------------------------------------------------------
SQL0104N An unexpected token "(" was found following "". Expected tokens may

include: ", FROM INTO ". SQLSTATE=42601

Sorry, you will have to name your "table" as in:

select * from (
select x.*, rownumber() over () as rn from ps_ledger_adb_mtd x
) Y where rn < 10

Lets hope it works now, I dont have the chance of trying

/Lennart

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Re: limiting rows returned in a sql select

comp.databases.ibm-db2 comp.databases.ibm-db2

Re: limiting rows returned in a sql select - 08-20-2003 , 01:25 PM

Re: limiting rows returned in a sql select - 08-25-2003 , 05:03 PM

Re: limiting rows returned in a sql select - 08-25-2003 , 10:41 PM