Re: Understanding sequence function

Thank you Tom & Thomas.

On 3 August 2012 15:32, Thomas Kellerer <spam_eater(at)gmx(dot)net> wrote:

> 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(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/**mailpref/pgsql-novice<http://www.postgresql.org/mailpref/pgsql-novice>
>