What is default sort in Informix SE ???
If I do not have ORDER BY in my select statement, what sort will be by
default??? As my experience, it will be rowid. But it is not in following
case.
-- table info --
DBSCHEMA Schema Utility INFORMIX-SQL Version 7.24.UC1
create table "randy".oe_acctstore
(
acct integer,
store char(3),
storename char(20),
primary key (acct,store) constraint "randy".u88794_2503
);
revoke all on "randy".oe_acctstore from "public";create index "randy".ix88794_1 on "randy".oe_acctstore (acct);
create index "randy".ix88794_2 on "randy".oe_acctstore (store);
-- select statement --
select *, rowid
from oe_acctstore
where acct = 12
-- result set --
acct store storename rowid
12 S12 6
12 S13 7
12 S14 8
12 S2 4
12 S21 5
12 S3 9
I want the result set is sort by rowid without adding ORDER BY rowid(too
many statement to modify). Any help is truly appreciated.
Randy
Randy Hao wrote in message <7l87nk$be2$1@nntp3.atl.mindspring.net>...
>If I do not have ORDER BY in my select statement, what sort will be by
>default??? As my experience, it will be rowid. But it is not in following
>case.
>
>-- table info --
>DBSCHEMA Schema Utility INFORMIX-SQL Version 7.24.UC1
>create table "randy".oe_acctstore
> (
> acct integer,
> store char(3),
> storename char(20),
> primary key (acct,store) constraint "randy".u88794_2503
> );
>revoke all on "randy".oe_acctstore from "public";>create index "randy".ix88794_1 on "randy".oe_acctstore (acct);
>create index "randy".ix88794_2 on "randy".oe_acctstore (store);
>
>-- select statement --
>select *, rowid
>from oe_acctstore
>where acct = 12>
>-- result set --
> acct store storename rowid
> 12 S12 6
> 12 S13 7
> 12 S14 8
> 12 S2 4
> 12 S21 5
> 12 S3 9
>
>I want the result set is sort by rowid without adding ORDER BY rowid(too
>many statement to modify). Any help is truly appreciated.
>
>Randy
>
>
Without an order by, there is no sorting unless needed to meet other
conditions (GROUP BY, for example). In this case, it appears that the
system transversed the index instead of doing a sequential scan of the
table.
One of the concepts of relational databases is that the data is stored and
retrieved without regard to ANY default order.