Re: How to get the first row in a selection of rows
Re: The suggestion below.
For some reason 'first 1' does not appear in my Informix published syntax
definitions for SELECT or its sub clauses.
Can you tell me does 'forst 1' imply any ordering or is it simply the first
one that happens to be listed according to the where clause. So if I want
the row with the next chronological value in my oid field I still need my
order by clause ?
i.e. I need to do this.
Select first 1 <cols>
into ...
from <table>
where oid > some val
order by oid
OR
Does the 'first 1' imply some ordering ON oid on its own.
i.e. this will get me the row with the next ordered oid
Select first 1 <cols>
into ...
from <table>
where oid > some val
----- Forwarded by Andrew Hardy/MAIN/MC1 on 15/10/2003 10:21 -----
|---------+---------------------------->
| | gsturkenboom@lic.|
| | co.nz |
| | Sent by: |
| | owner-informix-li|
| | st@iiug.org |
| | |
| | |
| | 14/10/2003 20:26 |
| | |
|---------+---------------------------->
>--------------------------------------------------------------------------------------------------------------------------------------------------|
| |
| To: informix-list@iiug.org |
| cc: |
| Subject: Re: How to get the first row in a selection of rows |
>--------------------------------------------------------------------------------------------------------------------------------------------------|
Have you tried this
Select first 1 <cols>
into ...
from <table>
where <where clause>
"Andrew Hardy"
<Andrew.Hardy@mar To:
informix-list@iiug.org
coni.com> cc:
Sent by: Subject: How to get the
first row in a selection of rowS
owner-informix-li
st@iiug.org
15/10/2003 05:00
I have a select statement whch retrieves a number of rows. Given the
underlying condition, I want the fastest way just to get the first of the
selected rows.
Andrew H.
Detail
===========================================================================================
I create a cursor 'userFileRead_nextOidCursor' based on the folloing SELECT
select {+INDEX(user_file_read_table, oid_index)} oid, f2, f3, f4 from
user_file_read_table
where oid > x order by oid
Then I get just the next ( i.e. the first ) row.
EXEC SQL FETCH NEXT userFileRead_nextOidCursor INTO :the_oid, v2, v3, v4
Then I close the cursor.
Can I do this faster ? May be it would be faster in one statement ?
The best I can think of so far is two statements.
select {+INDEX(user_file_read_table, oid_index)} MIN(oid) into required_oid
from user_file_read_table where oid > x
select {+INDEX(user_file_read_table, oid_index)} oid, f2, f3, f4 into
the_oid, v2, v3, v4 from user_file_read_table where oid = required_oid
Any suggestions. Speed is my main priority. But may be its as fast as it
can be with the cursor.
sending to informix-list
sending to informix-list
sending to informix-list
FIRST 1 does NOT imply any ordering. You have to specify ordering
explicitly. You can find up-to-date books at
http://www-3.ibm.com/software/data/informix/pubs/library/ids_73.html if
you're using IDS 7.3x or at
http://www-3.ibm.com/software/data/informix/pubs/library/ids_94.html if
you're using IDS 9.4 etc. Look for "IBM Informix Guide to SQL: Syntax".
Gorazd
"Andrew Hardy" <Andrew.Hardy@marconi.com> wrote in message
news:bmj4mq$hp8$1@terabinaries.xmission.com...
>
> Re: The suggestion below.
>
> For some reason 'first 1' does not appear in my Informix published syntax
> definitions for SELECT or its sub clauses.
>
> Can you tell me does 'forst 1' imply any ordering or is it simply the
first
> one that happens to be listed according to the where clause. So if I want
> the row with the next chronological value in my oid field I still need my
> order by clause ?
>
> i.e. I need to do this.
>
> Select first 1 <cols>
> into ...
> from <table>
> where oid > some val
> order by oid>
> OR
>
> Does the 'first 1' imply some ordering ON oid on its own.
>
> i.e. this will get me the row with the next ordered oid
>
> Select first 1 <cols>
> into ...
> from <table>
> where oid > some val>
>
> ----- Forwarded by Andrew Hardy/MAIN/MC1 on 15/10/2003 10:21 -----
> |---------+---------------------------->
> | | gsturkenboom@lic.|
> | | co.nz |
> | | Sent by: |
> | | owner-informix-li|
> | | st@iiug.org |
> | | |
> | | |
> | | 14/10/2003 20:26 |
> | | |
> |---------+---------------------------->
>
>---------------------------------------------------------------------------
-----------------------------------------------------------------------|
> |
|
> | To: informix-list@iiug.org
|
> | cc:
|
> | Subject: Re: How to get the first row in a selection of rows
|
>
>---------------------------------------------------------------------------
-----------------------------------------------------------------------|
>
>
>
>
>
> Have you tried this
>
> Select first 1 <cols>
> into ...
> from <table>
> where <where clause>>
>
>
>
> "Andrew Hardy"
>
> <Andrew.Hardy@mar To:
> informix-list@iiug.org
>
> coni.com> cc:
>
> Sent by: Subject: How to get the
> first row in a selection of rowS
> owner-informix-li
>
> st@iiug.org
>
>
>
> 15/10/2003 05:00
>
>
>
>
>
>
>
> I have a select statement whch retrieves a number of rows. Given the
> underlying condition, I want the fastest way just to get the first of the
> selected rows.
>
> Andrew H.
>
>
>
>
> Detail
>
============================================================================
===============
>
>
>
> I create a cursor 'userFileRead_nextOidCursor' based on the folloing
SELECT
>
> select {+INDEX(user_file_read_table, oid_index)} oid, f2, f3, f4 from
> user_file_read_table
> where oid > x order by oid
>
> Then I get just the next ( i.e. the first ) row.
>
> EXEC SQL FETCH NEXT userFileRead_nextOidCursor INTO :the_oid, v2, v3, v4
>
> Then I close the cursor.
>
> Can I do this faster ? May be it would be faster in one statement ?
>
> The best I can think of so far is two statements.
>
> select {+INDEX(user_file_read_table, oid_index)} MIN(oid) into
required_oid
> from user_file_read_table where oid > x
> select {+INDEX(user_file_read_table, oid_index)} oid, f2, f3, f4 into
> the_oid, v2, v3, v4 from user_file_read_table where oid = required_oid
>
> Any suggestions. Speed is my main priority. But may be its as fast as it
> can be with the cursor.
>
>
>
>
>
> sending to informix-list
>
>
>
>
>
> sending to informix-list
>
>
>
>
> sending to informix-list