Re: distinct on
From: Thomas Kellerer <thomas.kellerer_at_mgm-tp.com>
Date: Thu, 18 Oct 2012 09:05:29 +0200
Message-ID: <507FAA39.1020905_at_mgm-tp.com>
Jonathan Lewis, 18.10.2012 08:57:
>
> How does the Postgres specification define the requirements for the columns
> other than the on() column ?
>
> Is it "one row at (pseudo-)random from all the rows which match this
> specific value of the on() column", or is there any indication that the
> other columns could ALL be chosen randomly so that the output wasn't a row
> that actually existed, or is there any indication that the result will be
> deterministic in a particular way ?
>
Date: Thu, 18 Oct 2012 09:05:29 +0200
Message-ID: <507FAA39.1020905_at_mgm-tp.com>
Jonathan Lewis, 18.10.2012 08:57:
>
> How does the Postgres specification define the requirements for the columns
> other than the on() column ?
>
> Is it "one row at (pseudo-)random from all the rows which match this
> specific value of the on() column", or is there any indication that the
> other columns could ALL be chosen randomly so that the output wasn't a row
> that actually existed, or is there any indication that the result will be
> deterministic in a particular way ?
>
It uses the ordering defined by the ORDER BY if there is one, otherwise it's undefined:
From the manual:
"Note that the "first row" of each set is unpredictable unless ORDER BY is used to ensure that the desired row appears first"
So in the originally posted example it *is* actually undefined as no ORDER BY was used
The "equivalent" (of undefined) in my solution using row_number() would be to remove the order by in the over() clause
Thomas
-- http://www.freelists.org/webpage/oracle-lReceived on Thu Oct 18 2012 - 09:05:29 CEST