How do I get DESC order?

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

By the way, I'm using SQL Server Express, if it makes any difference.

|||

hi,

views, as tables, do not have an order at all.. in fact yoe are executing a SELECT ... FROM view .. ORDER BY ...;

this is the relevant (!) step in your requirement.. the order by clause is a cursor task, not associated with the underlying table/view.. data inside a table (on which the view is defined) is not ordered

please have a look at wikipedia and here as well, where you can read

"..A view is a relational table, and the
relational model states that a table is a set of rows. Since sets are not sorted - per definition - the rows in a view are not ordered either. .."

and even

".. A view is a logical relational table, and the relational model mandates that a table is a set of rows, implying no sort order whatsoever. .."

so, what you are getting, is correct..

if you like, you can "open" the view (in SSMSE) and apply the required "sort" clause adding the ORDER BY specs..

regards

|||

Andrea Montanari wrote:

hi,

views, as tables, do not have an order at all.. in fact yoe are executing a SELECT ... FROM view .. ORDER BY ...;

this is the relevant (!) step in your requirement.. the order by clause is a cursor task, not associated with the underlying table/view.. data inside a table (on which the view is defined) is not ordered

please have a look at wikipedia and here as well, where you can read

"..A view is a relational table, and the
relational model states that a table is a set of rows. Since sets are not sorted - per definition - the rows in a view are not ordered either. .."

and even

".. A view is a logical relational table, and the relational model mandates that a table is a set of rows, implying no sort order whatsoever. .."

so, what you are getting, is correct..

if you like, you can "open" the view (in SSMSE) and apply the required "sort" clause adding the ORDER BY specs..

regards

Thank you for the very informative response as well as the links, Andrea. I was starting to go insane wondering what I was missing! Another day another lesson, I guess!

Once again, thanks for the info.