Problem with sequence nextval() in embedded SELECT statement

[pennstump]

I have an INSERT statement with an embedded SELECT statement similar to this:

INSERT INTO a SELECT nextval('a_ID_seq') AS ID,0 AS status,false AS void,b.val AS value,c.code AS code,'test' AS createUser FROM b,c WHERE b.val=c.val ORDER BY c.val,b.val

The issue stems from the ORDER BY statement. The point of this query is to sort these values and insert them with the nextval() of the 'a_ID_seq' sequence so they're inserted into Table 'a' in the same order as the ORDER BY. However, the ORDER BY works correctly, but the sequence is called in some order that I can only guess is before the ordering, e.g. it appears that the sequence's next value is assigned to the query results before the ORDER BY is executed, so when I sort the results by the original ORDER BY statement, the IDs are not sequential.

Here's how it should work out:
1,0,false,10,50,'test'
2,0,false,10,60,'test'
3,0,false,20,60,'test'
4,0,false,5,70,'test'

Here's how it does work out (dramatization):
2,0,false,10,50,'test'
3,0,false,10,60,'test'
4,0,false,20,60,'test'
1,0,false,5,70,'test'

The nextval() method is called and assigned before the records are sorted. How can I fix this? Is there a way in pure SQL that I can have the nextval() method called after the records have been sorted, so my IDs are sequential?

Thanks.

 

[feherke]

Hi

You already got the point. First sort it, then add the sequential value.

[b]insert[/b] [b]into[/b] a
[b]select[/b]
nextval([i]'a_ID_seq'[/i]),0,false,foo.*,[i]'test'[/i]
[b]from[/b] (
  [b]select[/b]
  val,c.code
  [b]from[/b] b
  [b]inner[/b] [b]join[/b] c [b]using[/b] (val)
  [b]order[/b] [b]by[/b] val
) foo

Feherke.

http://rootshell.be/~feherke/

 

[pennstump]

Great! Thanks. After some explicit CAST() calls, this worked perfectly.