Re: Is it possible to make the order of output the same as the order of input parameters? - Mailing list pgsql-general

From Tim Landscheidt
Subject Re: Is it possible to make the order of output the same as the order of input parameters?
Date
Msg-id m3r5kpt98k.fsf@passepartout.tim-landscheidt.de
Whole thread Raw
In response to Is it possible to make the order of output the same as the order of input parameters?  ("m. hvostinski" <makhvost@gmail.com>)
List pgsql-general
Stephen Frost <sfrost@snowman.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

pgsql-general by date:

Previous
From: Mathieu De Zutter
Date:
Subject: Re: What Linux edition we should chose?
Next
From: John R Pierce
Date:
Subject: Re: Exception while accessing database