Re: SQL Syntax for ordering a sequence - Mailing list pgsql-novice

Andreas,

Thank you. It worked like a charm!
I'm still wrapping my head around the FROM in the UPDATE - but it
worked perfectly.

I also just used the primary key to order the rows because it is in
creation order anyway.

Many thanks for taking the time to respond.

Cheers,
-Damian

On Tue, Dec 22, 2009 at 7:03 PM, A. Kretschmer
<andreas.kretschmer@schollglas.com> wrote:
> In response to Damian Carey :
>> Hello,
>>
>> Firstly, apologies for the genuine novice's novice question, but all
>> my trawling can't find an answer. Our skills are Java desktop RIA, and
>> we use Hibernate to do the majority of our SQL lifting - hence our
>> limitations in understanding what is probably basic SQL.  Any pointers
>> as to where to find an answer would be most appreciated.
>>
>> We have a "card" table (with typically a few thousand rows.)  It has a
>> currently unused column "cardnum" (an integer) that was originally
>> supposed to have a sequence updating it, but for some unknown reason
>> was left null - and now we need to use it.
>>
>> We can easily put a unique sequential value into each row thusly ...
>>
>> CREATE SEQUENCE card_num_seq START 1;
>> UPDATE card SET cardnum=nextval('card_num_seq') WHERE card.cardnum IS NULL;
>>
>> That is almost OK, but we would really like the sequence to be in
>> "creation order", and the above update is essentially random.
>>
>> The card table does have a column "creationdate" (a timestamp), so we
>> can determine the order that the sequence needs to be.
>>
>> Can anyone please point me towards the appropriate SQL syntax to add
>> the sequence to our card table in an appropriate card order?
>
> Okay, let me try to help you:
>
> test=# select * from damian ;
>  id |             ts
> ----+----------------------------
>    | 2009-12-22 08:51:29.629166
>    | 2009-12-22 08:46:29.629166
>    | 2009-12-22 08:56:29.629166
>    | 2009-12-22 09:06:53.325429
>    | 2009-12-22 08:26:53.325429
> (5 rows)
>
> test=*# create sequence s_damian;
> CREATE SEQUENCE
> test=*# update damian set id = nextval('s_damian') from (select ts from damian d2 order by ts) foo where
foo.ts=damian.ts;
> UPDATE 5
> test=*# select * from damian order by 2;
>  id |             ts
> ----+----------------------------
>  1 | 2009-12-22 08:26:53.325429
>  2 | 2009-12-22 08:46:29.629166
>  3 | 2009-12-22 08:51:29.629166
>  4 | 2009-12-22 08:56:29.629166
>  5 | 2009-12-22 09:06:53.325429
> (5 rows)
>
>
> HTH, Andreas
> --
> Andreas Kretschmer
> Kontakt:  Heynitz: 035242/47150,   D1: 0160/7141639 (mehr: -> Header)
> GnuPG: 0x31720C99, 1006 CCB4 A326 1D42 6431  2EB0 389D 1DC2 3172 0C99
>
> --
> Sent via pgsql-novice mailing list (pgsql-novice@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-novice
>