Re: DISTINCT ON changes sort order

On 04/24/2013 09:03 AM, Alexander Reichstadt wrote:

> The order is correct. Now from the outer SELECT I would expect then to get:
> 53
> 29
> 46

Please re-read the manual on DISTINCT ON.

"SELECT DISTINCT ON ( expression [, ...] ) keeps only the first row of
each set of rows where the given expressions evaluate to equal. The
DISTINCT ON expressions are interpreted using the same rules as for
ORDER BY (see above). Note that the "first row" of each set is
unpredictable unless ORDER BY is used to ensure that the desired row
appears first."

You're running into the "unpredictable" part in that stanza. The
distinct on may change the query plan, which will result in a different
ordering of tuples than your inner clause, unless you used an
optimization fence.

Use an order-by clause. It's good practice to choose the column you want
so that it always comes first, and eliminates other candidates.

--
Shaun Thomas
OptionsHouse | 141 W. Jackson Blvd. | Suite 500 | Chicago IL, 60604
312-676-8870
sthomas(at)optionshouse(dot)com

______________________________________________

See http://www.peak6.com/email_disclaimer/ for terms and conditions related to this email