Re: Extending my question. Was: The relational model and relationalalgebra - why did SQL become the industry standard?

"Steve Kass" <skass_at_drew.edu> wrote in message news:b2k7pe$57v$1_at_slb0.atl.mindspring.net...
> Paul,
>
> There's a distinction here that you may be missing. Let's name the
> two queries
>
> select distinct SAL from emp -- query A
> select distinct SAL from emp order by SAL -- query B
>
> Perhaps unwisely, SQL allows a query to specify a set (of rows) as well
> as to specify a result set (presentation of a set of rows).

Steve thanks for highlighting the distinction. I think the fact that I sort of missed it is evidence of the unwiseness of the SQL approach.

> Query A and Query B both specify a result set, although the ordering of
> the rows in the result is only specified in query B. Only Query A specifies
> a set, however, since ORDER BY is not a valid clause in a set
> specification -
> only in a result set specification. Some products might allow Query B to
> be used as a set specification, but, for example, SQL Server only allows it
> with its proprietary TOP feature, in which case the ORDER BY becomes
> a necessary part of the definition of the set of rows, and does not have
> anything to do with an ordering within the set.

FYI, DB2 V8 allows the following. Not sure if it is in the SQL standard

SELECT C1 FROM
   (SELECT C1 FROM T1
      UNION
    SELECT C1 FROM T2
    ORDER BY C1 ) AS UTABLE
ORDER BY ORDER OF UTABLE and

  SELECT EMP_ACT.EMPNO,PROJNO

     FROM EMP_ACT
     WHERE EMP_ACT.EMPNO IN
         (SELECT EMPLOYEE.EMPNO
          FROM EMPLOYEE
          ORDER BY SALARY DESC
          FETCH FIRST 10 ROWS ONLY)

Regards
Paul Vernon
Business Intelligence, IBM Global Services Received on Sat Feb 15 2003 - 13:29:32 CET