Re: Selecting "first" 30 rows
In article <3r1t7s$pof@glas.cork.cig.mot.com>
kenneall@glas.rtsg.mot.com "Liam Kenneally" writes:
> Having just moved into the Informix domain from Oracle, I am looking for
> the simplest way to select the first X number of rows from a table.
>
> When using Oracle the solution was:
>
> SELECT col FROM table WHERE ROWNUM < X+1;>
> Is there a similiar mechanism in Informix. The solution will need to be used
> when selecting data as a result of a JOIN. I have looked at using ROWID
> but with no great success.
Selecting where ROWID < X+1 returns the 'inuse' rows in the first X 'slots'
in the table. I can think of tow ways:
i) Informix 4GL:
DECLARE CURSOR ....
LET l_count = 0
FOREACH ...
LET l_count = l_count + 1
IF l_count = x THEN
EXIT FOREACH
END FOREACH
ii) Informix SQL:
INSERT INTO newtab SELECT * FROM table;
SELECT * FROM newtab WHERE ROWID <= X;
or
cluster the UNIQUE index you probably have on <table>
thereby getting physical & logical orders identical (until some
p*ll*ck updates it!) and use your original idea.
--
============================================================================
Sally Woolrich | This mail contains my personal
sally@excelsis.demon.co.uk | views not those of my employer!
============================================================================