Received: from localhost (unknown [200.46.204.184])
	by developer.postgresql.org (Postfix) with ESMTP id 9EE9F2E0043
	for <pgsql-sql-postgresql.org@developer.postgresql.org>;
	Tue, 22 Apr 2008 08:09:52 -0300 (ADT)
Received: from developer.postgresql.org ([200.46.204.71])
	by localhost (mx1.hub.org [200.46.204.184]) (amavisd-maia, port 10024)
	with ESMTP id 68762-04
	for <pgsql-sql-postgresql.org@developer.postgresql.org>;
	Tue, 22 Apr 2008 08:09:39 -0300 (ADT)
X-Greylist: delayed 00:06:40.898163 by SQLgrey-1.7.5
Received: from europa.inventum.cc (unknown [74.205.124.113])
	by developer.postgresql.org (Postfix) with ESMTP id B56DA2E0044
	for <pgsql-sql@postgresql.org>; Tue, 22 Apr 2008 08:09:44 -0300 (ADT)
Received: (qmail 15995 invoked from network); 22 Apr 2008 06:03:01 -0500
Received: from abts-ncr-dynamic-054.25.161.122.airtelbroadband.in (HELO
	?127.0.0.1?) (122.161.25.54)
	by padamj.com with SMTP; 22 Apr 2008 06:03:00 -0500
Message-ID: <480DC5E1.5060508@inventum.cc>
Date: Tue, 22 Apr 2008 16:32:57 +0530
From: Padam J Singh <padam.singh@inventum.cc>
Organization: Inventum  Technologies Pvt Ltd
User-Agent: Thunderbird 2.0.0.9 (Windows/20071031)
MIME-Version: 1.0
To: Nacef LABIDI <nacef.l@gmail.com>
CC: depesz@depesz.com, Roland Voegtli <voegtli@scnat.ch>,
	pgsql-sql@postgresql.org
Subject: Re: After updating dataset the record goes to the end of the dataset
References: <f16f7ea00804220254i671e446au7c50dca45dea10d7@mail.gmail.com>	
	<3B256469-4A00-44C3-8EB8-F49FA9F7259A@scnat.ch>	
	<f16f7ea00804220310m26753072mec98ef0bc6bb2c9e@mail.gmail.com>	
	<20080422101523.GB17162@depesz.com>
	<f16f7ea00804220331u226df1ean9a17e12d9324e078@mail.gmail.com>
In-Reply-To: <f16f7ea00804220331u226df1ean9a17e12d9324e078@mail.gmail.com>
X-Enigmail-Version: 0.95.6
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
X-Antivirus: avast! (VPS 080421-1, 04/21/2008), Outbound message
X-Antivirus-Status: Clean
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=1.773 tagged_above=0 required=5
	tests=HTML_MESSAGE=0.001, MIME_HTML_ONLY=1.672, RDNS_NONE=0.1
X-Spam-Level: *
X-Archive-Number: 200804/136
X-Sequence-Number: 30675

<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
  <meta content="text/html;charset=ISO-8859-1" http-equiv="Content-Type">
</head>
<body bgcolor="#ffffff" text="#000066">
<font size="-1"><font face="Helvetica, Arial, sans-serif">Nacef,<br>
<br>
Simply add a SERIAL column to your table. When you add a new row, a new
value will be stored in this column if you do not specify the column
name in the insert query.<br>
In your select query, always order by this column.<br>
When doing an update , do not update this column. When re-ordering,
update this column to indicate order. So if you want to move a row up,
issue two update queries, updating this column. Make sure you have some
primary key in this table to uniquely identify these row.<br>
<br>
Never rely on the internal implementation of a database to keep the
data in the order you insert/update.<br>
<br>
Padam<br>
</font></font><br>
Nacef LABIDI wrote:
<blockquote
 cite="mid:f16f7ea00804220331u226df1ean9a17e12d9324e078@mail.gmail.com"
 type="cite">You are right about the fact that the rows don't keep the
order they where created with at the start. I have verfied this.<br>
  <br>
I will explain more my case :<br>
  <br>
I am writing an application where here is some resources to plan events
on. I want to provide the user with the ability to customize the order
in which resources are displayed on the screen. So there is a mapping
between the rows positions in the dataset and they display positions.
After updating one of these resources the row jumps to the end of the
dataset so does the resource on the display. And the order gets
scrambled. I cannot set this order in the databe since it is
customizable for each user.<br>
  <br>
When I have tested this with SQLServer it works well, since the rows
doesn't change position on the DB.<br>
  <br>
I hope that you understand my issue and I will provide any explanations
if someting isn't clear enough.<br>
  <br>
Thanks to all<br>
Nacef<br>
  <br>
  <div class="gmail_quote">On Tue, Apr 22, 2008 at 12:15 PM, hubert
depesz lubaczewski &lt;<a moz-do-not-send="true"
 href="mailto:depesz@depesz.com">depesz@depesz.com</a>&gt; wrote:<br>
  <blockquote class="gmail_quote"
 style="border-left: 1px solid rgb(204, 204, 204); margin: 0pt 0pt 0pt 0.8ex; padding-left: 1ex;">
    <div class="Ih2E3d">On Tue, Apr 22, 2008 at 12:10:41PM +0200, Nacef
LABIDI wrote:<br>
&gt; Yes I don't issue any sort statement, and I indeed want the data
to be show<br>
&gt; as it is stored in the database. But after updating a row (I don't
update<br>
&gt; the ID, just some fields), it keeps its same place on the DB but
jumps to<br>
&gt; the end of the dataset and by the way to the end of the DBGrid.<br>
    <br>
    </div>
why do you think it stays in the same place in db?<br>
    <br>
besides - without "order by" you cannot depend on the order of rows.<br>
basically i treat them as in "random" order (which is not true, but<br>
helps me remember to never count on the "default" ordering.<br>
    <br>
depesz<br>
    <font color="#888888"><br>
--<br>
quicksil1er: "postgres is excellent, but like any DB it requires a<br>
highly paid DBA. &nbsp;here's my CV!" :)<br>
    <a moz-do-not-send="true" href="http://www.depesz.com/"
 target="_blank">http://www.depesz.com/</a> - blog dla ciebie (i moje
CV)<br>
    </font></blockquote>
  </div>
  <br>
</blockquote>
<br>
<pre class="moz-signature" cols="72">-- 
PGP Id 9EED2E09</pre>
</body>
</html>