I am using Access 2003 as a front-end to a SQL Server 2005 database.
I make design changes using SQL Server Management Studio. I have a
table that includes a datetime column. I create a view and sort by
the datetime field. When I initially look at the result it is sorted
correctly. Then I save the view and re-open it and it is not sorted.
I've simplified the view so it only contains the date field and it
still does not sort. Here is the view:
SELECT TOP (100) PERCENT Period_Date
FROM dbo.Period_Summary
ORDER BY Period_Date DESC

The date seems to be a random order.
I don't have this problem in the SQL Server 2000 version of the
database.

Help please!
Thanks,
Jerry

The bottom line is that you should treat tables and views as unordered sets
or rows. SQL Server does not honor the ORDER BY specification in a view for
result set ordering. If you need an ordered result, you must specify ORDER
BY when selecting from the view/table.


--
Hope this helps.

Dan Guzman
SQL Server MVP

"Jer" <jerry.w.black@saic.com> wrote in message
news:1170287064.047611.235820@p10g2000cwp.googlegr oups.com...
>I am using Access 2003 as a front-end to a SQL Server 2005 database.
> I make design changes using SQL Server Management Studio. I have a
> table that includes a datetime column. I create a view and sort by
> the datetime field. When I initially look at the result it is sorted
> correctly. Then I save the view and re-open it and it is not sorted.
> I've simplified the view so it only contains the date field and it
> still does not sort. Here is the view:
> SELECT TOP (100) PERCENT Period_Date
> FROM dbo.Period_Summary
> ORDER BY Period_Date DESC
>
> The date seems to be a random order.
> I don't have this problem in the SQL Server 2000 version of the
> database.
>
> Help please!
> Thanks,
> Jerry
>

Just to add-on to Dan's response: in SQL Server 2000 using ORDER BY and TOP
100 PERCENT in views seemed to be a loophole that worked fine. In SQL Server
2005 that has been corrected and ORDER BY does not guarantee ordered
results. This is described very well in SQL Server 2005 BOL under the
section "Sorting Rows with ORDER BY":

"Although the view definition contains an ORDER BY clause, that ORDER BY
clause is used only to determine the rows returned by the TOP clause. When
querying the view itself, SQL Server does not guarantee the results will be
ordered, unless you specify so explicitly."

Regards,

Plamen Ratchev
http://www.SQLStudio.com

On Jan 31, 4:44 pm, "Jer" <jerry.w.bl...@saic.com> wrote:
> I am using Access 2003 as a front-end to a SQL Server 2005 database.
> I make design changes using SQL Server Management Studio. I have a
> table that includes a datetime column. I create a view and sort by
> the datetime field. When I initially look at the result it is sorted
> correctly. Then I save the view and re-open it and it is not sorted.
> I've simplified the view so it only contains the date field and it
> still does not sort. Here is the view:
> SELECT TOP (100) PERCENT Period_Date
> FROM dbo.Period_Summary
> ORDER BY Period_Date DESC
>
> The date seems to be a random order.
> I don't have this problem in the SQL Server 2000 version of the
> database.
>
> Help please!
> Thanks,
> Jerry

I now remember reading about this in one of my SQLServer books. But
as Plaman says, the Order By worked in SQLServer 2000.
Thanks Dan and Plamen for the reminder.

Jer (jerry.w.black@saic.com) writes:
> I now remember reading about this in one of my SQLServer books. But
> as Plaman says, the Order By worked in SQLServer 2000.

No, it seemed to work. But that was just by chance.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx