From grupos@carvalhaes.net Tue Apr  26 03:13:57 2005
X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org
Received: from localhost (unknown [200.46.204.144])
	by svr1.postgresql.org (Postfix) with ESMTP id DAFF753B49
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Tue, 26 Apr 2005 00:13:57 -0300 (ADT)
Received: from svr1.postgresql.org ([200.46.204.71])
	by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024)
	with ESMTP id 42789-01
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Tue, 26 Apr 2005 03:13:52 +0000 (GMT)
Received: from nitrogenio.carvalhaes.net (unknown [200.175.206.50])
	by svr1.postgresql.org (Postfix) with ESMTP id BC3AC53497
	for <pgsql-sql@postgresql.org>; Tue, 26 Apr 2005 00:13:15 -0300 (ADT)
Received: from [192.168.0.199] ([192.168.0.199]) (authenticated bits=0)
	by nitrogenio.carvalhaes.net (8.13.3/8.13.1) with ESMTP id
	j3Q33ll8002213
	for <pgsql-sql@postgresql.org>; Tue, 26 Apr 2005 00:03:48 -0300
Message-ID: <426DB1A0.2000604@carvalhaes.net>
Date: Tue, 26 Apr 2005 00:12:32 -0300
From: Rodrigo Carvalhaes <grupos@carvalhaes.net>
Reply-To: grupos@carvalhaes.net
User-Agent: Mozilla Thunderbird 1.0 (Windows/20041206)
X-Accept-Language: en-us, en
MIME-Version: 1.0
Cc: pgsql-sql@postgresql.org
Subject: UPDATE WITH ORDER BY
References: <fddc0c5705042511464f94be68@mail.gmail.com>
	<20050425202406.GA3289@winnie.fuhr.org>
In-Reply-To: <20050425202406.GA3289@winnie.fuhr.org>
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
X-Triade-MailScanner-Information: Por favor,
	entre em contato para obter detalhes
X-Triade-MailScanner: Found to be clean
X-MailScanner-From: grupos@carvalhaes.net
X-Virus-Scanned: by amavisd-new at hub.org
X-Spam-Status: No, hits=1.815 tagged_above=0 required=5 tests=HTML_50_60,
	HTML_MESSAGE, HTML_TEXT_AFTER_BODY, HTML_TITLE_EMPTY, MIME_HTML_ONLY,
	SUBJ_ALL_CAPS
X-Spam-Level: *
X-Archive-Number: 200504/378
X-Sequence-Number: 21271

<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
  <meta content="text/html;charset=ISO-8859-1" http-equiv="Content-Type">
  <title></title>
</head>
<body bgcolor="#ffffff" text="#000000">
<div class="moz-text-html" lang="x-western">
<tt>Hi Guys!<br>
<br>
I need to make an UPDATE on a column reordering it with a sequence
using order by a description.<br>
Confusing??? Well.. Let me give an example...<br>
<br>
Today, my table it's organized like this:<br>
<br>
Code&nbsp;&nbsp;&nbsp;&nbsp; / Description<br>
9&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Orange<br>
15&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Apple<br>
1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Pear<br>
3&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Tomato<br>
<br>
I wanna to reorganize (reordering the code from 1 to ... ordering by
description)<br>
</tt><tt><br>
Code&nbsp;&nbsp;&nbsp;&nbsp; / Description<br>
</tt><tt>1 &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Apple</tt><br>
<tt>2&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Orange<br>
3&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Pear<br>
4 &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Tomato</tt><br>
<tt><br>
I created a sequence but I am having no succes to use it because UPDATE
don't accept ORDER BY CLAUSE. The "ideal SQL" is UPDATE table SET code
= nextval('sequence') ORDER BY description <br>
<br>
I searched a lot on the NET without ant "tip" for my case. <br>
It's a very simple need but I am not able to solve it...<br>
<br>
Anyone knows how I can do it?<br>
<br>
Cheers,<br>
<br>
-- <br>
Rodrigo Carvalhaes<br>
<br>
</tt><!-- |**|end egp html banner|**| -->
</div>
</body>
<br />--
<br />Esta mensagem foi verificada pelo sistema de antivírus e 
<br /> acredita-se estar livre de perigo.
</html>



From ramakrishnanm@pervasive-postgres.com Wed Apr  27 09:13:58 2005
X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org
Received: from localhost (unknown [200.46.204.144])
	by svr1.postgresql.org (Postfix) with ESMTP id 4A9925372E
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Wed, 27 Apr 2005 06:13:58 -0300 (ADT)
Received: from svr1.postgresql.org ([200.46.204.71])
	by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024)
	with ESMTP id 90757-07
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Wed, 27 Apr 2005 09:13:55 +0000 (GMT)
Received: from ausimss.pervasive.com (ausimss.pervasive.com [66.45.103.246])
	by svr1.postgresql.org (Postfix) with ESMTP id 66A8953717
	for <pgsql-sql@postgresql.org>; Wed, 27 Apr 2005 06:13:50 -0300 (ADT)
Received: from ausbayes1.aus.pervasive.com ([172.16.8.6]) by
	ausimss.pervasive.com with InterScan Messaging Security Suite;
	Wed, 27 Apr 2005 04:13:42 -0500
Received: from ausmail2k3.aus.pervasive.com ([172.16.4.8]) by
	ausbayes1.aus.pervasive.com with Microsoft SMTPSVC(5.0.2195.6713);
	Wed, 27 Apr 2005 04:13:39 -0500
Received: from BLRMAIL1.aus.pervasive.com ([10.3.16.60]) by
	ausmail2k3.aus.pervasive.com with Microsoft SMTPSVC(5.0.2195.6713);
	Wed, 27 Apr 2005 04:13:38 -0500
Subject: Re: UPDATE WITH ORDER BY
Date: Tue, 26 Apr 2005 12:28:46 +0530
Message-ID: 
 <02767D4600E59A4487233B23AEF5C5992A4080@blrmail1.aus.pervasive.com>
X-MS-Has-Attach: 
MIME-Version: 1.0
Content-Type: multipart/alternative;
	boundary="----_=_NextPart_001_01C54A2D.641C623D"
X-MS-TNEF-Correlator: 
Thread-Topic: [SQL] UPDATE WITH ORDER BY
Thread-Index: AcVKDp1JOPLewss4SEGh6uvx11C7ugAHAfIA
X-MimeOLE: Produced By Microsoft Exchange V6.0.6603.0
content-class: urn:content-classes:message
From: "Ramakrishnan Muralidharan" <ramakrishnanm@pervasive-postgres.com>
To: <grupos@carvalhaes.net>
Cc: <pgsql-sql@postgresql.org>
X-OriginalArrivalTime: 27 Apr 2005 09:13:38.0117 (UTC)
	FILETIME=[6597E750:01C54B09]
X-Virus-Scanned: by amavisd-new at hub.org
X-Spam-Status: No, hits=0.485 tagged_above=0 required=5 tests=AWL,
	DATE_IN_PAST_24_48, HTML_50_60, HTML_MESSAGE, HTML_TITLE_EMPTY,
	SUBJ_ALL_CAPS
X-Spam-Level: 
X-Archive-Number: 200504/407
X-Sequence-Number: 21300

This is a multi-part message in MIME format.

------_=_NextPart_001_01C54A2D.641C623D
Content-Type: text/plain;
	charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

         Hi,
=20
            To my knowledge it is not possible to sort and update the =
code in a single update statement. I have done it through a simple =
function. I have given the function below.=20
=20
CREATE OR REPLACE FUNCTION SortCode()
RETURNS INT4 AS $$
DECLARE
  rRec RECORD;
BEGIN
  PERFORM SETVAL( 'test1_code_seq' , 1 , false );
  FOR rRec IN  (SELECT * FROM TEST1 ORDER BY DESCRIPTION)  LOOP
    UPDATE TEST1 SET CODE =3D nextval( 'test1_code_seq' ) WHERE =
DESCRIPTION =3D rRec.DESCRIPTION;
  END LOOP;=20
  RETURN 0;
END;
$$ language 'plpgsql';

following is the data used for testing
=20
create table test1( code serial , description varchar( 25 ) )
=20
insert into test1 values( 9,'Orange');
insert into test1 values(15,'Apple');
insert into test1 values(1,'Pear');
insert into test1 values(3,'Tomato');

Regards,
R.Muralidharan
=20

-----Original Message-----
From: pgsql-sql-owner@postgresql.org =
[mailto:pgsql-sql-owner@postgresql.org]On Behalf Of Rodrigo Carvalhaes
Sent: Tuesday, April 26, 2005 8:43 AM
Cc: pgsql-sql@postgresql.org
Subject: [SQL] UPDATE WITH ORDER BY


Hi Guys!

I need to make an UPDATE on a column reordering it with a sequence using =
order by a description.
Confusing??? Well.. Let me give an example...

Today, my table it's organized like this:

Code     / Description
9          Orange
15         Apple
1          Pear
3          Tomato

I wanna to reorganize (reordering the code from 1 to ... ordering by =
description)

Code     / Description
1          Apple
2          Orange
3          Pear
4          Tomato

I created a sequence but I am having no succes to use it because UPDATE =
don't accept ORDER BY CLAUSE. The "ideal SQL" is UPDATE table SET code =
=3D nextval('sequence') ORDER BY description=20

I searched a lot on the NET without ant "tip" for my case.=20
It's a very simple need but I am not able to solve it...

Anyone knows how I can do it?

Cheers,

--=20
Rodrigo Carvalhaes



--=20
Esta mensagem foi verificada pelo sistema de antiv=EDrus e=20
acredita-se estar livre de perigo.=20


------_=_NextPart_001_01C54A2D.641C623D
Content-Type: text/html;
	charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<META HTTP-EQUIV=3D"Content-Type" CONTENT=3D"text/html; =
charset=3Diso-8859-1">
<TITLE></TITLE>

<META content=3D"MSHTML 6.00.2800.1498" name=3DGENERATOR></HEAD>
<BODY text=3D#000000 bgColor=3D#ffffff>
<DIV><SPAN class=3D857063906-26042005><FONT face=3DArial color=3D#0000ff =

size=3D2>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; =
Hi,</FONT></SPAN></DIV>
<DIV><SPAN class=3D857063906-26042005><FONT face=3DArial color=3D#0000ff =

size=3D2></FONT></SPAN>&nbsp;</DIV>
<DIV><SPAN class=3D857063906-26042005><FONT face=3DArial color=3D#0000ff =

size=3D2>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbs=
p; To my=20
knowledge it is not possible to sort and update the code in a single =
update=20
statement. I have done it through a simple function. I have given the =
function=20
below. </FONT></SPAN></DIV>
<DIV><SPAN class=3D857063906-26042005><FONT face=3DArial color=3D#0000ff =

size=3D2></FONT></SPAN>&nbsp;</DIV>
<DIV><SPAN class=3D857063906-26042005><FONT face=3DArial color=3D#0000ff =
size=3D2>CREATE=20
OR REPLACE FUNCTION SortCode()<BR>RETURNS INT4 AS =
$$<BR>DECLARE<BR>&nbsp; rRec=20
RECORD;<BR>BEGIN<BR>&nbsp; PERFORM SETVAL( 'test1_code_seq' , 1 , false=20
);<BR>&nbsp; FOR rRec IN&nbsp; (SELECT * FROM TEST1 ORDER BY =
DESCRIPTION)&nbsp;=20
LOOP<BR>&nbsp;&nbsp;&nbsp; UPDATE TEST1 SET CODE =3D nextval( =
'test1_code_seq' )=20
WHERE DESCRIPTION =3D rRec.DESCRIPTION;<BR>&nbsp; END LOOP; <BR>&nbsp; =
RETURN=20
0;<BR>END;<BR>$$ language 'plpgsql';<BR></FONT></SPAN></DIV>
<DIV><SPAN class=3D857063906-26042005><FONT face=3DArial color=3D#0000ff =

size=3D2>following is the data used for testing</FONT></SPAN></DIV>
<DIV><SPAN class=3D857063906-26042005><FONT face=3DArial color=3D#0000ff =

size=3D2></FONT></SPAN>&nbsp;</DIV>
<DIV><SPAN class=3D857063906-26042005><FONT face=3DArial color=3D#0000ff =
size=3D2>create=20
table test1( code serial , description varchar( 25 ) =
)</FONT></SPAN></DIV>
<DIV><FONT face=3DArial color=3D#0000ff size=3D2></FONT>&nbsp;</DIV>
<DIV><SPAN class=3D857063906-26042005><FONT face=3DArial color=3D#0000ff =
size=3D2>insert=20
into test1 values( 9,'Orange');<BR>insert into test1=20
values(15,'Apple');<BR>insert into test1 values(1,'Pear');<BR>insert =
into test1=20
values(3,'Tomato');<BR></FONT></SPAN></DIV>
<DIV><SPAN class=3D857063906-26042005><FONT face=3DArial color=3D#0000ff =

size=3D2>Regards,</FONT></SPAN></DIV>
<DIV><SPAN class=3D857063906-26042005><FONT face=3DArial color=3D#0000ff =

size=3D2>R.Muralidharan</FONT></SPAN></DIV>
<DIV><SPAN class=3D857063906-26042005><FONT face=3DArial color=3D#0000ff =

size=3D2>&nbsp;</DIV></FONT></SPAN>
<BLOCKQUOTE>
  <DIV class=3DOutlookMessageHeader dir=3Dltr align=3Dleft><FONT =
face=3DTahoma=20
  size=3D2>-----Original Message-----<BR><B>From:</B>=20
  pgsql-sql-owner@postgresql.org =
[mailto:pgsql-sql-owner@postgresql.org]<B>On=20
  Behalf Of </B>Rodrigo Carvalhaes<BR><B>Sent:</B> Tuesday, April 26, =
2005 8:43=20
  AM<BR><B>Cc:</B> pgsql-sql@postgresql.org<BR><B>Subject:</B> [SQL] =
UPDATE WITH=20
  ORDER BY<BR><BR></FONT></DIV>
  <DIV class=3Dmoz-text-html lang=3Dx-western><TT>Hi Guys!<BR><BR>I need =
to make an=20
  UPDATE on a column reordering it with a sequence using order by a=20
  description.<BR>Confusing??? Well.. Let me give an =
example...<BR><BR>Today, my=20
  table it's organized like this:<BR><BR>Code&nbsp;&nbsp;&nbsp;&nbsp; /=20
  Description<BR>9&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; =

  Orange<BR>15&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;=20
  Apple<BR>1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;=20
  Pear<BR>3&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;=20
  Tomato<BR><BR>I wanna to reorganize (reordering the code from 1 to ... =

  ordering by description)<BR></TT><TT><BR>Code&nbsp;&nbsp;&nbsp;&nbsp; =
/=20
  Description<BR></TT><TT>1 =
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;=20
  =
Apple</TT><BR><TT>2&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;=
=20
  Orange<BR>3&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; =
Pear<BR>4=20
  &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; =
Tomato</TT><BR><TT><BR>I=20
  created a sequence but I am having no succes to use it because UPDATE =
don't=20
  accept ORDER BY CLAUSE. The "ideal SQL" is UPDATE table SET code =3D=20
  nextval('sequence') ORDER BY description <BR><BR>I searched a lot on =
the NET=20
  without ant "tip" for my case. <BR>It's a very simple need but I am =
not able=20
  to solve it...<BR><BR>Anyone knows how I can do =
it?<BR><BR>Cheers,<BR><BR>--=20
  <BR>Rodrigo=20
  Carvalhaes<BR><BR></TT><!-- |**|end egp html banner|**| =
--></DIV><BR>--=20
  <BR>Esta mensagem foi verificada pelo sistema de antiv=EDrus e =
<BR>acredita-se=20
  estar livre de perigo. </BLOCKQUOTE></BODY></HTML>

------_=_NextPart_001_01C54A2D.641C623D--



From ch@rodos.fzk.de Tue Apr  26 09:08:45 2005
X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org
Received: from localhost (unknown [200.46.204.144])
	by svr1.postgresql.org (Postfix) with ESMTP id 189F9545D3
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Tue, 26 Apr 2005 06:08:45 -0300 (ADT)
Received: from svr1.postgresql.org ([200.46.204.71])
	by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024)
	with ESMTP id 28860-08
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Tue, 26 Apr 2005 09:08:40 +0000 (GMT)
Received: from fzkmail2.fzk.de (fzkmail2.fzk.de [141.52.27.52])
	by svr1.postgresql.org (Postfix) with SMTP id 97ADC545B4
	for <pgsql-sql@postgresql.org>; Tue, 26 Apr 2005 06:08:36 -0300 (ADT)
Received: FROM resy5.fzk.de BY fzkmail2.fzk.de ;
 Tue Apr 26 11:08:18 2005 +0200
Received: from rodos.fzk.de (resy6.fzk.de [141.52.68.10])
	by resy5.fzk.de (8.12.7/8.12.7/SuSE Linux 0.6) with ESMTP id
	j3Q98EsG001193; Tue, 26 Apr 2005 11:08:14 +0200
Message-ID: <426E04FD.318032AA@rodos.fzk.de>
Date: Tue, 26 Apr 2005 11:08:13 +0200
From: Christoph Haller <ch@rodos.fzk.de>
X-Mailer: Mozilla 4.77 [en] (X11; U; HP-UX B.10.20 9000/780)
X-Accept-Language: en
MIME-Version: 1.0
To: grupos@carvalhaes.net
Cc: pgsql-sql@postgresql.org
Subject: Re: UPDATE WITH ORDER BY
References: <fddc0c5705042511464f94be68@mail.gmail.com>
	<20050425202406.GA3289@winnie.fuhr.org>
	<426DB1A0.2000604@carvalhaes.net>
Content-Type: text/plain; charset=iso-8859-2
Content-Transfer-Encoding: 7bit
X-Virus-Scanned: by AMaViS - amavis-milter (http://www.amavis.org/)
X-Virus-Scanned: by amavisd-new at hub.org
X-Spam-Status: No, hits=0.13 tagged_above=0 required=5 tests=AWL,
	UPPERCASE_25_50
X-Spam-Level: 
X-Archive-Number: 200504/379
X-Sequence-Number: 21272

> Rodrigo Carvalhaes wrote:
> 
> Hi Guys!
> 
> I need to make an UPDATE on a column reordering it with a sequence
> using order by a description.
> Confusing??? Well.. Let me give an example...
> 
> Today, my table it's organized like this:
> 
> Code     / Description
> 9          Orange
> 15         Apple
> 1          Pear
> 3          Tomato
> 
> I wanna to reorganize (reordering the code from 1 to ... ordering by
> description)
> 
> Code     / Description
> 1          Apple
> 2          Orange
> 3          Pear
> 4          Tomato
> 
> I created a sequence but I am having no succes to use it because
> UPDATE don't accept ORDER BY CLAUSE. The "ideal SQL" is UPDATE table
> SET code = nextval('sequence') ORDER BY description
> 
> I searched a lot on the NET without ant "tip" for my case.
> It's a very simple need but I am not able to solve it...
> 
> Anyone knows how I can do it?
> 
> Cheers,
> 
> --
> Rodrigo Carvalhaes
> 
I doubt this can be done by a single SQL command. 
My approach is a function. I did: 
CREATE TABLE fruittable(
fruitcode INTEGER,
fruitname TEXT
);
INSERT INTO fruittable VALUES( 9,	   'Orange' );
INSERT INTO fruittable VALUES( 15,	   'Apple'  );
INSERT INTO fruittable VALUES( 1,	   'Pear'   );
INSERT INTO fruittable VALUES( 3,	   'Tomato' );
SELECT * FROM fruittable ORDER BY fruitname ;
 fruitcode | fruitname 
-----------+-----------
        15 | Apple
         9 | Orange
         1 | Pear
         3 | Tomato

CREATE OR REPLACE FUNCTION reorder_fruitcode() RETURNS INTEGER AS '

DECLARE
 newcode INTEGER ;
 fruitrecord RECORD ;

BEGIN
 newcode := 1 ;
 
 FOR fruitrecord IN SELECT * FROM fruittable ORDER BY fruitname LOOP
 
  RAISE NOTICE ''fruitname is %'', fruitrecord.fruitname ;
  UPDATE fruittable SET fruitcode = newcode 
   WHERE fruitname = fruitrecord.fruitname ;
 
  newcode := newcode + 1 ;
 
 END LOOP ;
 
 RETURN 1;
END;
' LANGUAGE plpgsql;

SELECT reorder_fruitcode();
SELECT * FROM fruittable ORDER BY fruitname ;
 fruitcode | fruitname 
-----------+-----------
         1 | Apple
         2 | Orange
         3 | Pear
         4 | Tomato
(4 rows)
Voila. 

Regards, Christoph



From grupos@carvalhaes.net Tue Apr  26 17:04:17 2005
X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org
Received: from localhost (unknown [200.46.204.144])
	by svr1.postgresql.org (Postfix) with ESMTP id 653005372C
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Tue, 26 Apr 2005 14:04:17 -0300 (ADT)
Received: from svr1.postgresql.org ([200.46.204.71])
	by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024)
	with ESMTP id 54951-07
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Tue, 26 Apr 2005 17:04:07 +0000 (GMT)
Received: from nitrogenio.carvalhaes.net (unknown [200.175.206.50])
	by svr1.postgresql.org (Postfix) with ESMTP id 5F4CD53875
	for <pgsql-sql@postgresql.org>; Tue, 26 Apr 2005 14:04:04 -0300 (ADT)
Received: from [192.168.0.199] ([192.168.0.199]) (authenticated bits=0)
	by nitrogenio.carvalhaes.net (8.13.4/8.13.4) with ESMTP id
	j3QGsaRD003474; Tue, 26 Apr 2005 13:54:41 -0300
Message-ID: <426E6736.8030403@carvalhaes.net>
Date: Tue, 26 Apr 2005 13:07:18 -0300
From: Rodrigo Carvalhaes <grupos@carvalhaes.net>
Reply-To: grupos@carvalhaes.net
User-Agent: Mozilla Thunderbird 1.0 (Windows/20041206)
X-Accept-Language: en-us, en
MIME-Version: 1.0
To: Christoph Haller <ch@rodos.fzk.de>
Cc: pgsql-sql@postgresql.org
Subject: Re: UPDATE WITH ORDER BY
References: <fddc0c5705042511464f94be68@mail.gmail.com>
	<20050425202406.GA3289@winnie.fuhr.org>
	<426DB1A0.2000604@carvalhaes.net> <426E04FD.318032AA@rodos.fzk.de>
In-Reply-To: <426E04FD.318032AA@rodos.fzk.de>
Content-Type: text/html; charset=ISO-8859-2
Content-Transfer-Encoding: quoted-printable
X-Triade-MailScanner-Information: Por favor,
	entre em contato para obter detalhes
X-Triade-MailScanner: Found to be clean
X-MailScanner-From: grupos@carvalhaes.net
X-Virus-Scanned: by amavisd-new at hub.org
X-Spam-Status: No, hits=1.837 tagged_above=0 required=5 tests=AWL, HTML_20_30,
	HTML_MESSAGE, HTML_TEXT_AFTER_BODY, HTML_TITLE_EMPTY,
	MIME_HTML_ONLY
X-Spam-Level: *
X-Archive-Number: 200504/384
X-Sequence-Number: 21277

<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
  <meta content=3D"text/html;charset=3DISO-8859-2" http-equiv=3D"Content-Ty=
pe">
  <title></title>
</head>
<body bgcolor=3D"#ffffff" text=3D"#000000">
Thanksyou and Franz for your help. Simple and efficient... I was
blind.... The plpgsql "for" is the perfect solution<br>
<br>
It was great. Have a nice week!!!<br>
<br>
Cheers,<br>
<br>
Rodrigo Carvalhaes<br>
<br>
Christoph Haller wrote:
<blockquote cite=3D"mid426E04FD.318032AA@rodos.fzk.de" type=3D"cite">
  <blockquote type=3D"cite">
    <pre wrap=3D"">Rodrigo Carvalhaes wrote:

Hi Guys!

I need to make an UPDATE on a column reordering it with a sequence
using order by a description.
Confusing??? Well.. Let me give an example...

Today, my table it's organized like this:

Code     / Description
9          Orange
15         Apple
1          Pear
3          Tomato

I wanna to reorganize (reordering the code from 1 to ... ordering by
description)

Code     / Description
1          Apple
2          Orange
3          Pear
4          Tomato

I created a sequence but I am having no succes to use it because
UPDATE don't accept ORDER BY CLAUSE. The "ideal SQL" is UPDATE table
SET code =3D nextval('sequence') ORDER BY description

I searched a lot on the NET without ant "tip" for my case.
It's a very simple need but I am not able to solve it...

Anyone knows how I can do it?

Cheers,

--
Rodrigo Carvalhaes

    </pre>
  </blockquote>
  <pre wrap=3D""><!---->I doubt this can be done by a single SQL command.=
=20
My approach is a function. I did:=20
CREATE TABLE fruittable(
fruitcode INTEGER,
fruitname TEXT
);
INSERT INTO fruittable VALUES( 9,	   'Orange' );
INSERT INTO fruittable VALUES( 15,	   'Apple'  );
INSERT INTO fruittable VALUES( 1,	   'Pear'   );
INSERT INTO fruittable VALUES( 3,	   'Tomato' );
SELECT * FROM fruittable ORDER BY fruitname ;
 fruitcode | fruitname=20
-----------+-----------
        15 | Apple
         9 | Orange
         1 | Pear
         3 | Tomato

CREATE OR REPLACE FUNCTION reorder_fruitcode() RETURNS INTEGER AS '

DECLARE
 newcode INTEGER ;
 fruitrecord RECORD ;

BEGIN
 newcode :=3D 1 ;
=20
 FOR fruitrecord IN SELECT * FROM fruittable ORDER BY fruitname LOOP
=20
  RAISE NOTICE ''fruitname is %'', fruitrecord.fruitname ;
  UPDATE fruittable SET fruitcode =3D newcode=20
   WHERE fruitname =3D fruitrecord.fruitname ;
=20
  newcode :=3D newcode + 1 ;
=20
 END LOOP ;
=20
 RETURN 1;
END;
' LANGUAGE plpgsql;

SELECT reorder_fruitcode();
SELECT * FROM fruittable ORDER BY fruitname ;
 fruitcode | fruitname=20
-----------+-----------
         1 | Apple
         2 | Orange
         3 | Pear
         4 | Tomato
(4 rows)
Voila.=20

Regards, Christoph

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
      subscribe-nomail command to <a class=3D"moz-txt-link-abbreviated" hre=
f=3D"mailto:majordomo@postgresql.org">majordomo@postgresql.org</a> so that =
your
      message can get through to the mailing list cleanly

  </pre>
</blockquote>
<br>
<pre class=3D"moz-signature" cols=3D"72">--=20


Abra=E7o,

Rodrigo Carvalhaes
DBA PostgreSQL
Moderador grupo siga-br</pre>
</body>
<br />--
<br />Esta mensagem foi verificada pelo sistema de antiv=EDrus e=20
<br /> acredita-se estar livre de perigo.
</html>





From greg@turnstep.com Wed Apr  27 12:05:54 2005
X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org
Received: from localhost (unknown [200.46.204.144])
	by svr1.postgresql.org (Postfix) with ESMTP id 5B97853B4E
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Wed, 27 Apr 2005 09:05:54 -0300 (ADT)
Received: from svr1.postgresql.org ([200.46.204.71])
	by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024)
	with ESMTP id 30263-10
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Wed, 27 Apr 2005 12:05:49 +0000 (GMT)
Received: from biglumber.com (biglumber.com [207.228.252.42])
	by svr1.postgresql.org (Postfix) with SMTP id 6A5D753AC6
	for <pgsql-sql@postgresql.org>; Wed, 27 Apr 2005 09:05:46 -0300 (ADT)
Received: (qmail 24378 invoked from network); 27 Apr 2005 12:05:49 -0000
Received: from unknown (HELO localhost) (207.228.252.42)
	by 0 with SMTP; 27 Apr 2005 12:05:49 -0000
From: "Greg Sabino Mullane" <greg@turnstep.com>
To: pgsql-sql@postgresql.org
Subject: Re: UPDATE WITH ORDER BY
X-PGP-Key: 2529 DF6A B8F7 9407 E944  45B4 BC9B 9067 1496 4AC8
X-Request-PGP: 
 http://www.biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8
In-Reply-To: <426DB1A0.2000604@carvalhaes.net>
Date: Wed, 27 Apr 2005 12:05:49 -0000
X-Mailer: JoyMail 1.48
Message-ID: <f4c00ccf32fdcc228c81c684be9b58d5@biglumber.com>
X-Virus-Scanned: by amavisd-new at hub.org
X-Spam-Status: No, hits=0.253 tagged_above=0 required=5 tests=AWL,
	UPPERCASE_25_50
X-Spam-Level: 
X-Archive-Number: 200504/388
X-Sequence-Number: 21281


-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1


(Please send email as text, not HTML)

> I need to make an UPDATE on a column reordering it with a sequence
> using order by a description
> ...

BEGIN;
CREATE SEQUENCE fruit_seq;
CREATE TABLE newfruit AS SELECT nextval('fruit_seq')::int AS newid, * FROM fruit ORDER BY lower(description);
ALTER TABLE newfruit DROP COLUMN id;
ALTER TABLE newfruit RENAME COLUMN newid TO id;
DROP TABLE fruit;
ALTER TABLE newfruit RENAME TO fruit;
DROP SEQUENCE fruit_seq;
COMMIT;
SELECT * FROM fruit ORDER BY id ASC;


- --
Greg Sabino Mullane greg@turnstep.com
PGP Key: 0x14964AC8 200504270805
http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8
-----BEGIN PGP SIGNATURE-----

iD8DBQFCb4ArvJuQZxSWSsgRAnSGAJwMbp6qoN3H2wFedsgn8N55kV6zUQCg77Qn
VWsBmACCUFIdzRDRRalG6KI=
=y3G9
-----END PGP SIGNATURE-----





From tharakan@gmail.com Tue Feb  19 12:49:07 2008
Received: from localhost (unknown [200.46.204.184])
	by postgresql.org (Postfix) with ESMTP id 28F9B2E003D
	for <pgsql-sql-postgresql.org@postgresql.org>;
	Tue, 19 Feb 2008 08:49:07 -0400 (AST)
Received: from postgresql.org ([200.46.204.71])
	by localhost (mx1.hub.org [200.46.204.184]) (amavisd-maia, port 10024)
	with ESMTP id 75893-09 for <pgsql-sql-postgresql.org@postgresql.org>;
	Tue, 19 Feb 2008 08:48:57 -0400 (AST)
Received: from fg-out-1718.google.com (fg-out-1718.google.com [72.14.220.155])
	by postgresql.org (Postfix) with ESMTP id 7A24C2E003A
	for <pgsql-sql@postgresql.org>; Tue, 19 Feb 2008 08:49:04 -0400 (AST)
Received: by fg-out-1718.google.com with SMTP id 22so1688521fge.23
	for <pgsql-sql@postgresql.org>; Tue, 19 Feb 2008 04:49:02 -0800 (PST)
DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=gamma;
	h=domainkey-signature:received:received:message-id:date:from:to:subject:mime-version:content-type;
	bh=rYCsfg59V/7+AKTSXNq5eDS8jYDqOgLIj/NQoYyCB/E=;
	b=Atbs/H6U9zdHYudYaLtIvDIuB5HOCe6I0ILbKxP/Boan1/kYNblhMKRjvgc7/Vgr9IlPDOnEsdWLGNmh/8ZjMVMWZ++FeiGYZzsrKzaUOhXhETppT3bMonEnXa/S2to4Irgwz58diLtTJwH1au1dXYJ2xHuSWvhg31wwwKSzUWY=
DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma;
	h=message-id:date:from:to:subject:mime-version:content-type;
	b=KpCi0llMOUak6h7dFYli5fxQEXM3BjhcqFAm5PuOr/17waTiF3ZqnHFMAzKg7eGLU0twJWuDC8mjCunmBpY/Da8pD3TS3/ela9sFZdPUq7azr3yqrEll+Yl8MVENiZ0dQvKF3pNYFipQBAGE7Gz0v9va3rDqRDIExEWQFnreHzo=
Received: by 10.82.108.9 with SMTP id g9mr12246428buc.37.1203425342179;
	Tue, 19 Feb 2008 04:49:02 -0800 (PST)
Received: by 10.82.112.18 with HTTP; Tue, 19 Feb 2008 04:49:02 -0800 (PST)
Message-ID: <36af4bed0802190449m247ee430ge37bee5acdcb6e13@mail.gmail.com>
Date: Tue, 19 Feb 2008 18:19:02 +0530
From: "Robins Tharakan" <tharakan@gmail.com>
To: pgsql-sql <pgsql-sql@postgresql.org>
Subject: UPDATE with ORDER BY
MIME-Version: 1.0
Content-Type: multipart/alternative;
	boundary="----=_Part_14902_17182007.1203425342168"
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Archive-Number: 200802/149
X-Sequence-Number: 30240

------=_Part_14902_17182007.1203425342168
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
Content-Disposition: inline

Hi,

I know this kind of a question is asked earlier, but I couldn't find an
answer there (in the previous round of posting).

Instead of wanting to update the first record in an UPDATE .. ORDER BY
condition, (because of triggers that act downward) what I want is that all
records be updated, but in a given order, for e.g. ascending on date.

Is a FOR LOOP my best bet or is something like UPDATE .... ORDER BY in the
offing ?

Thanks
*Robins*

------=_Part_14902_17182007.1203425342168
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
Content-Disposition: inline

<span style="font-family: verdana,sans-serif;">Hi,</span><br style="font-family: verdana,sans-serif;"><br style="font-family: verdana,sans-serif;"><span style="font-family: verdana,sans-serif;">I know this kind of a question is asked earlier, but I couldn&#39;t find an answer there (in the previous round of posting).</span><br style="font-family: verdana,sans-serif;">
<br style="font-family: verdana,sans-serif;"><span style="font-family: verdana,sans-serif;">Instead of wanting to update the first record in an UPDATE .. ORDER BY condition, (because of triggers that act downward) what I want is that all records be updated, but in a given order, for e.g. ascending on date.</span><br style="font-family: verdana,sans-serif;">
<br style="font-family: verdana,sans-serif;"><span style="font-family: verdana,sans-serif;">Is a FOR LOOP my best bet or is something like UPDATE .... ORDER BY in the offing ?</span><br style="font-family: verdana,sans-serif;">
<br style="font-family: verdana,sans-serif;"><span style="font-family: verdana,sans-serif;">Thanks</span><br style="font-family: verdana,sans-serif;"><b style="font-family: verdana,sans-serif;">Robins</b><br style="font-family: verdana,sans-serif;">

------=_Part_14902_17182007.1203425342168--