To: pgsql-sql@postgresql.org
From: Thomas Kellerer <spam_eater@gmx.net>
Subject: Re: After updating dataset the record goes to the end of the dataset
Date: Tue, 22 Apr 2008 12:11:42 +0200
Lines: 26
Message-ID: <fukdku$6qa$1@ger.gmane.org>
References: <f16f7ea00804220254i671e446au7c50dca45dea10d7@mail.gmail.com>
Mime-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US;
	rv:1.8.1.12) Gecko/20080213 Thunderbird/2.0.0.12 Mnenhy/0.7.5.666
In-Reply-To: <f16f7ea00804220254i671e446au7c50dca45dea10d7@mail.gmail.com>

Nacef LABIDI, 22.04.2008 11:54:
> Hi all,
> 
> I am using Postgres in a Delphi application through ODBC. I am having an 
> issue with updating records.
> 
> When I create a dataset to get the records in a table then after I 
> update one of these records and then refresh the dataset, the record 
> goes to the end of the dataset. This is disappointing when editing 
> records on a DBGrid, where users find their updated records jump to the 
> end of the grid. Even after restarting the application, the updated 
> record keeps showing at the end of the grid. I have tried the same thing 
> with SQL Server and it works normally, so I thought it was a postgres 
> behvior.
> 
> Has anyone an idea about what could be the cause of such a behavior.

Rows in a relational database are not sorted. This is true for any RDBMS.

If you want to apply a certain sort order you *have* to use an ORDER BY clause for your SELECT. 
If the rows show up in the order you "expect" without an ORDER BY this is pure coincidence (even with SQL Server)

The simply solution is to add an ORDER BY that sorts e.g. by a timestamp that is set when the row is created.

Regards
Thomas