ORDER BY in a View 2

[alexkeel79]

I am trying to change the order of data I have in a SQL view but it does not let me use the ORDER BY command. Basically what I am trying to do is take a report that has statuses of 'High', 'Medium', and 'Low' and have the rows go in that order. When I use the GROUP BY command it defaults to sorting in an ascending order so my rows go 'High', 'Low', 'Medium'. What I have done before in the past is use the SWITCH function but since I am not able to use the ORDER BY command this is not working. Thanks for any help offered.

 

[mrdenny]

If you need to use the order by command in a view you need to use a SELECT TOP 100 PERCENT instead of just SELECT. That will let you put the order by in.

Denny

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

(My very old site)

 

[NoCoolHandle]

The reason SQL won't let you is that it is when you run the view that you should be sorting it. Otherwise what can happen is it gets sorted in the view one way and then the sql statemtn that uses is then sorts it in a different way. 2 x the work... basicly it tries to remove the option to build performatnce probalmes into your db.

If for some reason you really wnat to order your view it can be done (but be verrrrryyyyyy vvveeeeerrrrry carefull!)

the key is build a top 100 percent query and then oreder it. The optimizer sees the top clause and misses the 100%. But because a top clause normally uses a sort col to bringback a subset of data sql will let you put the order by in...

Again BE CAREFULL! my advise is order it when it comes out. The GroupBy will sometimes order things differently but usually it is asc or dsc, it just depends on HOW it does the gropuings. If it uses hash buckets rather than temp tables vers nested loopes etc...

HTH


Rob

 

[donutman]

Another generic way to change the collation sequence for a limited range of values is to use a case statement to make a Sort column or Group By field (in this case).
-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

 

[alexkeel79]

Thanks guys. I was able to use a case statement with a new attribute which I then used for the ORDER BY statement. Thanks for your help.