Re: SQL Syntax for ordering a sequence

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