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

From: Herouth Maoz <herouth(at)oumail(dot)openu(dot)ac(dot)il>
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: pgsql-sql(at)postgreSQL(dot)org
Subject: Re: [SQL] Finding the "most recent" rows
Date: 1999-04-29 10:28:38
Message-ID: l03130302b34ddfaad0be@[147.233.159.109]
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-sql

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

In response to

Responses

Browse pgsql-sql by date

  From Date Subject
Next Message Herouth Maoz 1999-04-29 11:00:22 Re: [SQL] Relating 1 table to another.
Previous Message Herouth Maoz 1999-04-29 10:04:54 Re: [SQL] Finding the "most recent" rows