Prev: SQLjobvis 3.0 (free) - Visualize your SQL agent job histories...
Next: [SS 2000] Mirroring of only 1 table
From: Gerry Hickman on 4 Mar 2010 06:57
Hi,

(SQL Server 2005)

It seems ORDER BY does not work when presenting data using a VIEW? If I
enter design mode and "execute SQL", the data is sorted, but if I "Open" the
view, the data is not sorted.

--
Gerry Hickman
London (UK)

From: Uri Dimant on 4 Mar 2010 07:06
Gerry
It does not guarantee the order....

Use the below

create view m_view
as
select col1,col2 from tbl
--order by col1 ---do not put the order by clause within a vew


--usage

select * from m_view order by ......


"Gerry Hickman" <gerry666uk2(a)newsgroup.nospam> wrote in message
news:%23vvBVH5uKHA.4908(a)TK2MSFTNGP06.phx.gbl...
> Hi,
>
> (SQL Server 2005)
>
> It seems ORDER BY does not work when presenting data using a VIEW? If I
> enter design mode and "execute SQL", the data is sorted, but if I "Open"
> the view, the data is not sorted.
>
> --
> Gerry Hickman
> London (UK)


From: Ami Levin on 4 Mar 2010 07:13
hi Gerry,

Views do not allow ORDER BY.
Since is a view is supposed to return a set, and since by definition a set
has no order, adding the ORDER BY clause to a view would render the result a
cursor and not a set.
SQL Server has a 'workaround' and allows you to add ORDER BY to views only
when TOP is also used.
TOP is not in the ANSI standard BTW.
I saw several cases where people abused this by specifying "TOP 100 PERCENT"
and adding the ORDER BY.
BUT... it is not guaranteed, again by definition, that you will get the
results sorted.
Instead, simply add ORDER BY to the SELECT query used to retrieve the data
and not to the view's definition.

SELECT *
FROM My_View
ORDER BY Some_Expression

HTH

Ami Levin
SQL Server MVP
http://www.dbsophic.com/


"Gerry Hickman" <gerry666uk2(a)newsgroup.nospam> wrote in message
news:%23vvBVH5uKHA.4908(a)TK2MSFTNGP06.phx.gbl...
> Hi,
>
> (SQL Server 2005)
>
> It seems ORDER BY does not work when presenting data using a VIEW? If I
> enter design mode and "execute SQL", the data is sorted, but if I "Open"
> the view, the data is not sorted.
>
> --
> Gerry Hickman
> London (UK)

From: Gerry Hickman on 4 Mar 2010 07:30
"Ami Levin" <ami(a)dbsophic.nospam.com> wrote in message
news:%2324hOP5uKHA.5316(a)TK2MSFTNGP05.phx.gbl...
> hi Gerry,
>
> Views do not allow ORDER BY.
> Since is a view is supposed to return a set, and since by definition a set
> has no order, adding the ORDER BY clause to a view would render the result
> a cursor and not a set.

OK, that explains it.

> SQL Server has a 'workaround' and allows you to add ORDER BY to views only
> when TOP is also used.
> TOP is not in the ANSI standard BTW.
> I saw several cases where people abused this by specifying "TOP 100
> PERCENT" and adding the ORDER BY.

Actually, the VIEW designer in SQL Server 2005 adds the TOP 100 PERCENT
automatically! But ORDER BY still does not work for me.

> BUT... it is not guaranteed, again by definition, that you will get the
> results sorted.
> Instead, simply add ORDER BY to the SELECT query used to retrieve the data
> and not to the view's definition.
>
> SELECT *
> FROM My_View
> ORDER BY Some_Expression

OK, so I have to accept the VIEW will not directly allow this. The reason I
designed these views was to run some quick checks against data in another
database. I used to have a number of saved queries, but it's time consuming
having to open the query, run the query, close the query. I thought I could
just have some VIEWs and just open them as needed.


--
Gerry Hickman
London (UK)

From: Tibor Karaszi on 4 Mar 2010 07:51
http://sqlblog.com/blogs/tibor_karaszi/archive/2007/11/28/sorted-views.aspx

--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi



"Gerry Hickman" <gerry666uk2(a)newsgroup.nospam> wrote in message
news:#vvBVH5uKHA.4908(a)TK2MSFTNGP06.phx.gbl...
> Hi,
>
> (SQL Server 2005)
>
> It seems ORDER BY does not work when presenting data using a VIEW? If I
> enter design mode and "execute SQL", the data is sorted, but if I "Open"
> the view, the data is not sorted.
>
> --
> Gerry Hickman
> London (UK)

 |  Next  |  Last
Pages: 1 2
Prev: SQLjobvis 3.0 (free) - Visualize your SQL agent job histories...
Next: [SS 2000] Mirroring of only 1 table