Обсуждение: 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 >