How to get dates in descending order.

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Express Edition and Compact Edition (2005)

How to get dates in descending order.

Author

Topic

Acoustic1978
Starting Member

7 Posts

Posted - 2007-09-21 : 13:09:22

Hey guys, I have a view with dates (TheDate) meant to be arranged in descending order. When I 'Execute SQL' while in the view, the DESC order works just fine and shows up with the latest date first going down. However, once I 'OPEN VIEW' the order keeps defaulting to ASCending order.

How do I keep it in DESC order for viewing? Here's the statement:

SELECT TOP (100) PERCENT TheDate
FROM dbo.MyDates
ORDER BY TheDate DESC

Kristen
Test

22859 Posts

Posted - 2007-09-21 : 13:17:36

You really shouldn't have TOP (100) PERCENT in a view - in fact that may be something that used to work in SQL 2000 and no longer does in SQL 2005.

You need to put the Order By in your select statement, something like this probably:


CREATE VIEW MyView
AS
SELECT TheDate
FROM dbo.MyDates
GO

SELECT TheDate
FROM MyView
ORDER BY TheDate DESC

Kristen

Acoustic1978
Starting Member

7 Posts

Posted - 2007-09-21 : 14:15:21

quote:
Originally posted by Kristen

You really shouldn't have TOP (100) PERCENT in a view - in fact that may be something that used to work in SQL 2000 and no longer does in SQL 2005.

You need to put the Order By in your select statement, something like this probably:

CREATE VIEW MyView
AS
SELECT TheDate
FROM dbo.MyDates
GO

SELECT TheDate
FROM MyView
ORDER BY TheDate DESC

Kristen

Thanks Kristen. I'm using SQL server 2005 express edition. It automatically creates the Top 100 line by default. I did try your method but it kept giving me this error:

The CREATE VIEW SQL construct or statement is not supported.

Kristen
Test

22859 Posts

Posted - 2007-09-22 : 02:23:43

"It automatically creates the Top 100 line by default"

What's the "it" you are referring to? Some application development tool?

"The CREATE VIEW SQL construct or statement is not supported"

That's well bizarre!

Actually, maybe you are in the wrong part of the SQL toolkit ... I found this which looks to be a similar issue:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1371509&SiteID=1

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources