Re: Inverted result set, why?

On Tue, 27 Jun 2000 jeam(at)themail(dot)com wrote:
>
> I SELECT * and the records are retrieved in the order I inserted them,
> and that's correct.

Any order is correct because the order in which rows are returned for an
SQL query is completely unspecified unless your query uses the 'order by'
clause.

> However, if I SELECT the records from a Delphi 5 application, using
> the BDE and the latest ODBC driver, the result set is inverted, i.e.,
> on the same index key, the last record I inserted is listed first and
> so on.

Nothing wrong with this.

> This forces me to use an index even for tables I don't need one. Or
> worse yet, I need to create a second, auxiliary field on the table and
> add it to the index when I have one in order to get the proper order
> of the inserted records.

Even if you do all these things you still have no guarantee that rows are
returned in any particular order. Indexes may result in more efficient
retrieval or sorting but by themselves don't force any order on the
result. The fact that some implementation, or even several
implementations, return rows in a certain order (with or without indices)
does not imply that other implementations or future versions of the same
database will do the same.

> I don't know if I should blame Borland's BDE or the ODBC driver.

You can't blame them because they are not in violation of the SQL
specification. Use an 'order by' clause and you get the desired
order.

Joachim