From: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
---|---|
To: | James David Smith <james(dot)david(dot)smith(at)gmail(dot)com> |
Cc: | pgsql-novice(at)postgresql(dot)org |
Subject: | Re: Understanding sequence function |
Date: | 2012-08-03 14:28:41 |
Message-ID: | 16623.1344004121@sss.pgh.pa.us |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-novice |
James David Smith <james(dot)david(dot)smith(at)gmail(dot)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
From | Date | Subject | |
---|---|---|---|
Next Message | Thomas Kellerer | 2012-08-03 14:32:33 | Re: Understanding sequence function |
Previous Message | James David Smith | 2012-08-03 13:59:01 | Understanding sequence function |