Thread: SQL Syntax for ordering a sequence
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?
Any pointers would be much appreciated!
Many thanks for your time,
-Damian
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
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
>