Posted by David Portas on 03/08/06 15:45

Serge Rielau wrote:

> Given that views get expanded into the query
> there is no technical reason for the presence of the order by columns in
> the view signature (the columns exposed by the view).

So what would you expect to be the order of rows output by ORDER BY
_ORDER_OF_v1_ in the following example? Are you sure you can define
that order for all possible queries? Does it make a difference if I
leave Z in the view or not?

I suspect that any deterministic logic you define is going to be so
obscure that it would outweigh any apparent attraction of the syntax
shortcut you are proposing. It would cause more confusion and
unjustified assumptions about what the "correct" order should be. In
other words it would add nothing in terms of clarity or concision.

CREATE TABLE tbl (x INTEGER NOT NULL, z INTEGER NOT NULL, PRIMARY KEY
(x,z));

INSERT INTO tbl (x,z) VALUES (100,1);
INSERT INTO tbl (x,z) VALUES (100,2);
INSERT INTO tbl (x,z) VALUES (200,0);
INSERT INTO tbl (x,z) VALUES (200,4);

CREATE VIEW v1 AS
SELECT x,z
FROM tbl
ORDER BY z /* note: not legal SQL */

SELECT x
FROM v1
GROUP BY x
ORDER BY _ORDER_OF_v1_ ;

--
David Portas, SQL Server MVP

Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.

SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--

[Back to original message]