12

This question just had to be in this site :)

ORDER BY is forbidden to use in a view, as I understood because of the possibility for multiple order by's when using this view.

I know that there are ways of bypassing this limitation, for example TOP 99.999999 PERCENT, but I would like to know what is the best practice, not how to hack it.

So, if I want to create views in my database for personal use, meaning I want to connect to the database and just see the data fixed and sorted, how should I do it if I can't order a view?

Currently in my SQL Server DB I have views with the TOP hack, and I'm using them a lot, but it feels wrong.

| improve this question | |
  • 1
    why not TOP 100%? – garik Jul 21 '11 at 11:44
  • @garik - because it still doesn't guarantee the ordering if I understand gbn's link correctly. It is just a more concise wrong answer – Jack Douglas Jul 21 '11 at 16:38
  • 1
    @Jack Douglas it was not an answer, it was a comment about top 99.999... – garik Jul 21 '11 at 19:00
  • @garik - in SQL Server 2008 the TOP 100 PERCENT is ignored, and only using 99 is working. I think it was fixed but my version isn't updated. – yellowblood Jul 24 '11 at 14:16
  • 1
    This is not a technical limitation. This is by design. That's why any workaround feels like a hack. Impose your ordering in the queries against the views. An order applies to a result-set (e.ge query output), not a collection of data (e.g. a view or table). – Nick Chammas Sep 7 '11 at 19:53
2

There is no such thing as an "ordered" view because a view is supposed to be a set of rows. Use a stored procedure if you want a fixed ordering.

| improve this answer | |
  • This is the answer that explains why you cannot impose an ordering inside a view definition. A set, by definition, does not have an order. When you combine TOP with ORDER BY, however, you are now defining a new set since the ordering affects which rows are "top"; this is why TOP enables you to use ORDER BY in a view definition. – Nick Chammas Sep 7 '11 at 19:55
9

Only the outermost ORDER BY will guarantee order

  • Any intermediate or internal ORDER BY is ignored.
    This includes ORDER BY in a view
  • There is no implied order in any table
  • There is no implied order from any index (clustered or not) on that table

Links

ORDER BY guarantees a sorted result only for the outermost SELECT statement of a query. For example, consider the following view definition: (and example follow that matches this question)

| improve this answer | |
  • Thanks for the clarification, but it's not the answer I was looking for :) I'm trying to see what kind of alternatives are there for the very convenient ORDER BY inside a view. – yellowblood Jul 24 '11 at 14:22
3

Add an ORDER BY to the output of the view i.e.

  SELECT whatever FROM MYVIEW ORDER BY whatever
| improve this answer | |
  • 1
    There is no implied order by clustered index. -1. – gbn Jul 21 '11 at 12:36
  • @SqlACID - good edit, +1 instead of -1 :) – Jack Douglas Jul 21 '11 at 18:58
  • This means you need to rewrite the order by clause every time you query the view, which is what we're trying to avoid when we use the order by inside the view. – yellowblood Jul 24 '11 at 14:20
  • as dportas said, a stored proc wrapper around the query would work, but probably not what you're looking for. – SqlACID Jul 25 '11 at 2:05
  • I actually tried stored procedure but it wasn't easy and quick enough to query or manipulate. Meh. – yellowblood Jul 25 '11 at 10:15

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.