Re: Extending my question. Was: The relational model and relationalalgebra - why did SQL become the industry standard?
Date: Sat, 15 Feb 2003 12:29:32 -0000
"Steve Kass" <skass_at_drew.edu> wrote in message
> 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
SELECT C1 FROM T2
ORDER BY C1 ) AS UTABLE
ORDER BY ORDER OF UTABLE and
FROM EMP_ACT WHERE EMP_ACT.EMPNO IN (SELECT EMPLOYEE.EMPNO FROM EMPLOYEE ORDER BY SALARY DESC FETCH FIRST 10 ROWS ONLY)
Business Intelligence, IBM Global Services Received on Sat Feb 15 2003 - 13:29:32 CET