pg.ddx.io pgsql-interfaces@postgresql.org mailing list archive
help / color / mirror / Atom feedInverted result set, why?
4+ messages / 4 participants
[nested] [flat]
* Inverted result set, why?
@ 2000-06-27 11:30 jeam@themail.com
0 siblings, 2 replies; 4+ messages in thread
From: jeam@themail.com @ 2000-06-27 11:30 UTC (permalink / raw)
To: pgsql-interfaces
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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Inverted result set, why?
@ 2000-06-29 15:01 Joachim Achtzehnter <joachim@kraut.bc.ca>
parent: jeam@themail.com
1 sibling, 0 replies; 4+ messages in thread
From: Joachim Achtzehnter @ 2000-06-29 15:01 UTC (permalink / raw)
To: pgsql-interfaces
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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Inverted result set, why?
@ 2000-06-30 02:14 Byron Nikolaidis <byron.nikolaidis@home.com>
parent: jeam@themail.com
1 sibling, 1 reply; 4+ messages in thread
From: Byron Nikolaidis @ 2000-06-30 02:14 UTC (permalink / raw)
To: jeam@themail.com; pgsql-interfaces
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
>
>
>
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Inverted result set, why?
@ 2000-06-30 02:35 phil@Stimpy.netroedge.com
parent: Byron Nikolaidis <byron.nikolaidis@home.com>
0 siblings, 0 replies; 4+ messages in thread
From: phil@Stimpy.netroedge.com @ 2000-06-30 02:35 UTC (permalink / raw)
To: Byron Nikolaidis <byron.nikolaidis@home.com>; pgsql-interfaces
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/~phil
PGP F16: 01 D2 FD 01 B5 46 F4 F0 3A 8B 9D 7E 14 7F FB 7A
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2000-06-30 02:35 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2000-06-27 11:30 Inverted result set, why? jeam@themail.com
2000-06-29 15:01 ` Joachim Achtzehnter <joachim@kraut.bc.ca>
2000-06-30 02:14 ` Byron Nikolaidis <byron.nikolaidis@home.com>
2000-06-30 02:35 ` phil@Stimpy.netroedge.com
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox