[thelist] mysql - null - returns results anyway

Jason Handby jason.handby at corestar.co.uk
Wed Dec 23 02:31:56 CST 2009

> > ... I often find myself using these sorts of quick-and-dirty
> > queries as the basis for views, in which case the extra
> > "order by" is useful and appropriate.
> ORDER BY is ~never~ appropriate for a view
> full stop

In the old days of MS-SQL 2000 I found myself writing ordered views a
lot. T-SQL wouldn't let you order a view unless you needed to -- for
example to return the first few records under a particular sort -- but I
discovered that you could use "SELECT TOP 100% ..... ORDER BY ..." to
return a sorted result set.

I lost count of how many of these views I had to fix when MS-SQL 2005
came along, and they had removed this quirk :-)  The syntax was still
valid but the results were no longer in any particular order.

And when you think about it this makes a lot of sense. Basically a view
defines a virtual table, returning a *set* of records, and there's no
reason to expect it to be in any kind of order, any more than you would
with the records in a table. A view is not a query, even though its
definition looks like one.

So, just to add to what Rudy said... don't do it; and even if it seems
to work now, that may not continue!


More information about the thelist mailing list