Thread: Understanding sequence function

James David Smith <james.david.smith@gmail.com> writes:
> 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.

That's right, and it's per SQL standard: conceptually, at least, ORDER
BY is done after calculation of the targetlist items.  Logically that's
necessary because ORDER BY can depend on a targetlist item (ye olde
"ORDER BY 1" syntax).

> I don't want it to do this.

You need a sub-select.  Something like this should do it:

SELECT ss.*, nextval('serial') as serial from
  ( SELECT id, date_time FROM test ORDER BY date_time DESC ) ss;

            regards, tom lane

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
tohave used it the wrong way around. I guess it gets the data, does the serial, and then does the order. I don't want
itto 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
usingnextval() 
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;