Retrieval Order on Select/FetchNext

[Klong]

When selecting and fetching multiple records using table I/O, is there any way to control the order that the records are retrieved? What determines the order records are fetched?

Thanks for you help.

Kevin Long
OneWorld XE SP16 Update 2 w/Oracle 8i on NT

 

[samlsc]

I'm also very interested in the answer as it seems that the ordering follows the way the data is inserted in the tables.

Any Business Functions which caters for Data Sorting on Fetch?

OneWorld XE B7333 SP16, INTEL WIN2000 SP2, SQL 7.0 SP2

 

[Sef]

Hi Kevin,

My 'Off the cuff' answer would be to expand/add an index which includes the item you wish to fetch. You can exclude this item in your select statement (whilst using the new index) and retrieve it in the fetch next statement.

Good luck,

Sef van den Nieuwelaar
Australia
B732 on NT, XE on NT, B732/A73 on AS400, B733 on NT

* Coming to a European City near you soon *

 

[wherdes]

The way you get the records depends on the index you have chosen. If you don't like the sorting order, make a new index and use this in the select statement.

Walter.

B7332 / XE / NT4 / SQL 7 / VisualStudio 6 SP5

 

[Zoltan_Gyimesi]

Hi,

Kevin, I think Walter and Sef already made the answer for your issue.

I am just curious that what happens when I select different index in the Select and in the FetchNext statement? Combinating further the scenario, when I select different index in the subsequent FetchNext operations?

I have never done it and I would like know that had anybody tested already these scenarios and what was the result?

I hope, this is not an independent issue which would be better to place in a new thread, because if this modifies the sorting behavour then it could be interesting for Kevin too. Do you agree?

Regards,

Zoltán

B7332 SP11, ESU 4116422, Intel NT4, SQL 7 SP1
(working with B7321, B7331, XE too)