"Damond Walker" <dwalker@black-oak.com> writes:
> Returned the following message under MS SQL Server 7.0:
>     ORDER BY items must appear in the select list if SELECT DISTINCT is
> specified.

Sure looks like that is the consensus answer to the semantics problem...
guess we should do the same.

			regards, tom lane

Thu, 21 Oct 1999 11:36:16 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: [HACKERS] distinct. Is this the correct behaviour? 
----- Original Message -----
> Hmph, so sybase hasn't thought through the implications of ORDER BY on
> a hidden column vs. DISTINCT either.  Can anyone try it on some other
> DBMSes?
>

Using the following script...

    create table t1(f1 int, f2 int);
    insert into t1(f1, f2) values(1,1);
    insert into t1(f1, f2) values(1,2);
    insert into t1(f1, f2) values(1,3);
    insert into t1(f1, f2) values(2,4);
    select distinct f1 from t1 order by f2;


Returned the following message under Oracle8 on NT:
    ORA-01791: not a SELECTed expression

Returned the following message under MS SQL Server 7.0:
    ORDER BY items must appear in the select list if SELECT DISTINCT is
specified.

I could try it on Oracle8i but I suspect the result will be the same.


Thu, 21 Oct 1999 09:57:16 -0700
"Damond Walker" <dwalker@black-oak.com>
Re: [HACKERS] distinct. Is this the correct behaviour?
> "Damond Walker" <dwalker@black-oak.com> writes:
> > Returned the following message under MS SQL Server 7.0:
> >     ORDER BY items must appear in the select list if SELECT DISTINCT is
> > specified.
> 
> Sure looks like that is the consensus answer to the semantics problem...
> guess we should do the same.

Added to TODO:

	* require SELECT DISTINCT target list to have all ORDER BY columns

-- 
  Bruce Momjian                        |  http://www.op.net/~candle
  maillist@candle.pha.pa.us            |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

Thu, 21 Oct 1999 12:58:32 -0400 (EDT)
Bruce Momjian <maillist@candle.pha.pa.us>
Re: [HACKERS] distinct. Is this the correct behaviour?