Re: Understanding sequence function

James David Smith, 03.08.2012 15:59:

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

5        | 2012-07-12 10:00:04           |       5

4        | 2012-07-12 10:00:03           |       6
3        | 2012-07-12 10:00:02           |       1
2        | 2012-07-12 10:00:01           |       4
1        | 2012-07-12 10:00:00           |       2

If you need to have a (guaranteed) consecutive numbering in your result set, use row_number():

SELECT id, date_time, row_number() over (order by date_time ASC) as serial

FROM test
ORDER BY date_time DESC;

--
Sent via pgsql-novice mailing list (pgsql-novice@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-novice