RE: Default Select Order
This is a multi-part message in MIME format.
------_=_NextPart_001_01C3C544.D8890614
Content-Type: text/plain;
charset="us-ascii"
Content-Transfer-Encoding: quoted-printable
You can use, the rowid, column.
=20
Ex:
=20
select c.rowid, c.code, c.name from customer c
where c.code =3D 3
order by 1=20
If the column rowid not exists you can define in your table with the
option " with rowid". See down,
=20
Creating a Rowid Column in a Fragmented Table
If, for some reason, you find that your applications must use a rowid
column
to access data in a fragmented table, you must create a rowid column for
the
fragmented table.
You can use the WITH ROWIDS clause of the CREATE TABLE statement to
create the rowid column at the same time that you create the table. When
you
issue the CREATE TABLE...WITH ROWIDS statement, the database server
creates a rowid column that adds 4 bytes to each row in the fragmented
table.
In addition, the database server creates an internal index on the rowid
column that it uses to access the data in the table. After the rowid
column is
created, the database server inserts a row in the sysfragments catalog
table,
which indicates the existence and attributes of the rowid column.
If you decide that you need a rowid column after you build the
fragmented
table, use the ADD ROWIDS clause of the ALTER TABLE statement or the
INIT
clause of the ALTER FRAGMENT statement.
Using Primary Keys Instead of Rowids
You can drop the rowid column from a fragmented table with the DROP
ROWIDS clause of the ALTER TABLE statement. For more information, see
the
ALTER TABLE statement in the Informix Guide to SQL: Syntax.
You cannot create or add a rowid column by naming it as one of the
columns
in a table that you create or alter. For example, you will receive an
error if you
execute the following statement:
CREATE TABLE test_table (rowid INTEGER, ....)
You will get the following error:
-227 DDL options on rowid are prohibited. error.
=20
=20
BYE,
=20
-----Mensaje original-----
De: Vijay [mailto:rudrappa@india.hp.com]=20
Enviado el: jueves, 18 de diciembre de 2003 5:11
Para: informix-list@iiug.org
Asunto: Default Select Order
=09
=09
Hi All,
=20
I have a Select * from <tab> where <col> =3D "some condition"
=20
Can i know wat is the default order in which the rows are
returned... Does it return the rows in the order in which they were
inserted.. I need this because now i am joining this to another table &
i need to get the records in the same order in which they are returned
using the single query... Please Advice...
=20
Regards,
Vijay Kumar.R
Software Engg.
HP-ISO Bangalore,
Ph : 205 2640
=20
------_=_NextPart_001_01C3C544.D8890614
Content-Type: text/html;
charset="us-ascii"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD><TITLE id=3DridTitle>Mensaje</TITLE>
<META http-equiv=3DContent-Type content=3D"text/html; =
charset=3Dus-ascii">
<STYLE>BODY {
MARGIN-TOP: 25px; FONT-SIZE: 10pt; MARGIN-LEFT: 25px; COLOR: #000000; =
FONT-FAMILY: Arial, Helvetica; BACKGROUND-COLOR: #ffffff
}
P.msoNormal {
MARGIN-TOP: 0px; FONT-SIZE: 10pt; MARGIN-LEFT: 0px; COLOR: #ffffcc; =
FONT-FAMILY: Helvetica, "Times New Roman"
}
LI.msoNormal {
MARGIN-TOP: 0px; FONT-SIZE: 10pt; MARGIN-LEFT: 0px; COLOR: #ffffcc; =
FONT-FAMILY: Helvetica, "Times New Roman"
}
</STYLE>
<META content=3D"MSHTML 6.00.2800.1276" name=3DGENERATOR></HEAD>
<BODY id=3DridBody style=3D"BACKGROUND-COLOR: #ffffff" bgColor=3D#ffffff =
background=3D"">
<DIV><SPAN class=3D707065108-18122003>You can use, the rowid, =
column.</SPAN></DIV>
<DIV><SPAN class=3D707065108-18122003></SPAN> </DIV>
<DIV><SPAN class=3D707065108-18122003>Ex:</SPAN></DIV>
<DIV><SPAN class=3D707065108-18122003></SPAN> </DIV>
<DIV><SPAN class=3D707065108-18122003> select c.rowid, =
c.code,=20
c.name from customer c</SPAN></DIV>
<DIV><SPAN class=3D707065108-18122003> where c.code =
=3D=20
3</SPAN></DIV>
<DIV><SPAN class=3D707065108-18122003> order by =
1</SPAN></DIV>
<DIV><SPAN class=3D707065108-18122003></SPAN> </DIV>
<DIV><SPAN class=3D707065108-18122003>If the column rowid not exists you =
can=20
define in your table with the option " with rowid". See =
down,</SPAN></DIV>
<DIV><SPAN class=3D707065108-18122003></SPAN> </DIV>
<DIV><SPAN class=3D707065108-18122003><B><FONT face=3DArial =
size=3D4>Creating a Rowid=20
Column in a Fragmented Table</DIV>
<DIV></B></FONT><FONT face=3D"Times New Roman" size=3D2>
<P align=3Dleft>If, for some reason, you find that your applications =
must use a=20
rowid column</P>
<P align=3Dleft>to access data in a fragmented table, you must create a =
rowid=20
column for the</P>
<P align=3Dleft>fragmented table.</P>
<P align=3Dleft>You can use the </FONT><FONT face=3D"Times New Roman" =
size=3D1>WITH=20
ROWIDS </FONT><FONT face=3D"Times New Roman" size=3D2>clause of the =
</FONT><FONT=20
face=3D"Times New Roman" size=3D1>CREATE TABLE </FONT><FONT =
face=3D"Times New Roman"=20
size=3D2>statement to</P>
<P align=3Dleft>create the rowid column at the same time that you create =
the=20
table. When you</P>
<P align=3Dleft>issue the </FONT><FONT face=3D"Times New Roman" =
size=3D1>CREATE=20
TABLE...WITH ROWIDS </FONT><FONT face=3D"Times New Roman" =
size=3D2>statement, the=20
database server</P>
<P align=3Dleft>creates a rowid column that adds 4 bytes to each row in =
the=20
fragmented table.</P>
<P align=3Dleft>In addition, the database server creates an internal =
index on the=20
rowid</P>
<P align=3Dleft>column that it uses to access the data in the table. =
After the=20
rowid column is</P>
<P align=3Dleft>created, the database server inserts a row in the =
</FONT><B><FONT=20
face=3D"Times New Roman" size=3D2>sysfragments </B></FONT><FONT=20
face=3D"Times New Roman" size=3D2>catalog table,</P>
<P align=3Dleft>which indicates the existence and attributes of the =
rowid=20
column.</P>
<P align=3Dleft>If you decide that you need a rowid column after you =
build the=20
fragmented</P>
<P align=3Dleft>table, use the </FONT><FONT face=3D"Times New Roman" =
size=3D1>ADD=20
ROWIDS </FONT><FONT face=3D"Times New Roman" size=3D2>clause of the =
</FONT><FONT=20
face=3D"Times New Roman" size=3D1>ALTER TABLE </FONT><FONT face=3D"Times =
New Roman"=20
size=3D2>statement or the </FONT><FONT face=3D"Times New Roman"=20
size=3D1>INIT</P></FONT><FONT face=3D"Times New Roman" size=3D2>
<P align=3Dleft>clause of the </FONT><FONT face=3D@@D