Re: 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