Re: Is it possible to make the order of output the same as the order of input parameters?

Stephen Frost <sfrost(at)snowman(dot)net> wrote:

>> > Just thinking about it now; do SQL's semantics say it'll always do
>> > the right thing? PG does in a couple of quick tests (i.e. one where
>> > customer is a small table and PG prefers a seqscan and where it's larger
>> > and prefers an index scan) but I'm not sure if this could change.

>> PostgreSQL's documentation on VALUES has at least no guaran-
>> tee of the order of data. I'd prefer David's solution :-).

> Uhm, that's why there's an explicit ORDER BY.. I seriously doubt that
> would ever be violated. If there was an approach suggested which didn't
> include an ORDER BY *somewhere*, I'd be suspect of it.

The query read:

| SELECT c.*
| FROM customer c, (
| SELECT *, row_number() OVER ()
| FROM (VALUES (23), (56), (2), (12), (10)) x) x(val,ord)
| WHERE c.id = x.val
| ORDER BY x.ord;

and the question is whether "VALUES (1), (2), (3)" will al-
ways return "(1)", "then" "(2)", "then" "(3)" and whether
"ROW_NUMBER() OVER ()" will always keep that order intact.

Tim