Re: [SQL] Finding the "most recent" rows

At 19:29 +0300 on 23/04/1999, Tom Lane wrote:

> I recall now that when I first heard of "SELECT DISTINCT ON field"
> I objected that the results weren't well-defined (since it's not clear
> how DISTINCT will choose which tuple to return). It might be that the
> SQL spec requires the first tuple to be chosen for each value of
> "field", which would allow the user to control the results by inserting
> a preceding ORDER BY step --- or skip the ORDER BY, if he doesn't really
> care which tuple he gets. That'd actually be a pretty cool design.
> Anyone have an SQL spec handy to check it?

DISTINCT ON is a non-standard structure. Your initial assertion, that it
will return different results in different implementations holds true.
Standard SQL should return the same result sets (order not important unless
ORDER BY was mentioned).

Tables are considered as unordered sets. There can not be anything in the
standard that relies on the order in the table. And what do you mean by
inserting a preceding ORDER BY step?

Herouth

--
Herouth Maoz, Internet developer.
Open University of Israel - Telem project
http://telem.openu.ac.il/~herutma