Thread: Inverted result set, why?
Hi there, Using the psql utility I insert three records into a 6.5.3 postgresql table with or without indexes. I SELECT * and the recordsare retrieved in the order I inserted them, and that's correct. However, if I SELECT the records from a Delphi 5 application, using the BDE and the latest ODBC driver, the result set isinverted, i.e., on the same index key, the last record I inserted is listed first and so on. 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 fieldon the table and add it to the index when I have one in order to get the proper order of the inserted records. I don't know if I should blame Borland's BDE or the ODBC driver. Any clues about this problem? Thanks in advance, Jorge. __________________________________________________________________ Make A Buck Or Two @ TheMail.com - Free Internet Email Sign-up today at http://www.themail.com/ref.htm?ref=908313
On Tue, 27 Jun 2000 jeam@themail.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
If you want a specific order based on the time, you could add a timestamp field to the table that would default to the current time when the record was inserted (see create table...default...) . Then you can "order by" on the timestamp, either ascending or descending if desired. Byron ----- Original Message ----- From: <jeam@themail.com> To: <pgsql-interfaces@postgresql.org> Sent: Tuesday, June 27, 2000 7:30 AM Subject: [INTERFACES] Inverted result set, why? > > Hi there, > > Using the psql utility I insert three records into a 6.5.3 postgresql table with or without indexes. I SELECT * and the records are retrieved in the order I inserted them, and that's correct. > > 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. > > 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. > > I don't know if I should blame Borland's BDE or the ODBC driver. > > Any clues about this problem? > > Thanks in advance, > > Jorge. > __________________________________________________________________ > Make A Buck Or Two @ TheMail.com - Free Internet Email > Sign-up today at http://www.themail.com/ref.htm?ref=908313 > > >
On Thu, Jun 29, 2000 at 10:14:39PM -0400, Byron Nikolaidis wrote: > If you want a specific order based on the time, you could add a timestamp > field to the table that would default to the current time when the record > was inserted (see create table...default...) . Then you can "order by" on > the timestamp, either ascending or descending if desired. That's exactly right. Most tables I deal with contain at least a timestamp field and a creator field (aka the source of the data). In general, if you think you'll need it, then you should store it. Storage space is realitively cheap. Good luck! Phil -- Philip Edelbrock -- IS Manager -- Edge Design, Corvallis, OR phil@netroedge.com -- http://www.netroedge.com/~philPGP F16:01 D2 FD 01 B5 46 F4 F0 3A 8B 9D 7E 14 7F FB 7A