DROP TABLE IF EXISTS test; DROP SEQUENCE IF EXISTS serial; CREATE TABLE test( id INTEGER, date_time TIMESTAMP); INSERT INTO test (id, date_time) VALUES ('1', '2012-07-12 10:00:00'), ('2', '2012-07-12 10:00:01'), ('3', '2012-07-12 10:00:02'), ('4', '2012-07-12 10:00:03'), ('5', '2012-07-12 10:00:04'), ('6', '2012-07-12 10:00:05'); CREATE SEQUENCE serial start 1; SELECT id, date_time, nextval('serial') as serial FROM test ORDER BY date_time DESC; _______________ The result of the select query is below. What I don't understand is why isn't the sequence going from 1-6? It seems to have used it the wrong way around. I guess it gets the data, does the serial, and then does the order. I don't want it to do this. Is there some way to get the sequence to generate itself while respecting the order of the select statement? id | date_time | serial ------------------------------------------------------------ 6 | 2012-07-12 10:00:05 | 6 5 | 2012-07-12 10:00:04 | 5 4 | 2012-07-12 10:00:03 | 4 3 | 2012-07-12 10:00:02 | 3 2 | 2012-07-12 10:00:01 | 2 1 | 2012-07-12 10:00:00 | 1 Thanks James
My assumption is, that the rows are first retrieved from the table (including the "generation" of the sequence numbers using nextval() That order is not specified.
Then, once those rows are retrieved, they are sorted. As it happens just the other way round in which they were retrieved. If you do some updates/deletes/inserts into the table you _could_ wind up with something like this:
id | date_time | serial ------------------------------------------------------------ 6 | 2012-07-12 10:00:05 | 3