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
5
  • 1
    why not TOP 100%?
    – garik
    Jul 21, 2011 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, 2011 at 16:38
  • 1
    @Jack Douglas it was not an answer, it was a comment about top 99.999...
    – garik
    Jul 21, 2011 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, 2011 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, 2011 at 19:53

3 Answers 3

Reset to default
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
1
  • 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, 2011 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
1
  • 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, 2011 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
5
  • 1
    There is no implied order by clustered index. -1.
    – gbn
    Jul 21, 2011 at 12:36
  • @SqlACID - good edit, +1 instead of -1 :)
    – Jack Douglas
    Jul 21, 2011 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, 2011 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, 2011 at 2:05
  • I actually tried stored procedure but it wasn't easy and quick enough to query or manipulate. Meh.
    – yellowblood
    Jul 25, 2011 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.