Hi everyone,
I'm trying to use the sequence function for something, but don't quite understand the intracy of how it works. A self contained example is below:
_______________
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