Sort column without ORDER BY
-
Is there any method to sort a column(numeric) without using ORDER BY clause.
-
Why?
_____________
Code for TallyGenerator -
if this is to get around the issue of "CANNOT USE ORDER BY IN A VIEW WITHOUT TOP CLAUSE", use
SELECT TOP 100 PERCENT
[...]
ORDER BY [...]
-
Don't use orderig in view.
It's really bad for performance.
You can always order records returned by view.
_____________
Code for TallyGenerator -
Make it a clustered index
-
Even with a clustered index, doesn't MS claim that there is no guarantee that they'll be in the order you want without the ORDER BY? I'm thinking specifically of caching issues as to potential causes of issues when avoiding an ORDER BY clause.
-
I am dying to hear the answer to why they do not want to use an ORDER BY clause
-
Clustered index won't help if you use join queries.
And nothing won't help if you don't.
_____________
Code for TallyGenerator -
David's right, if you want to guarantee the data retrieved is sorted then you have to declare ORDER BY.
The ANSI standard quotes something like this..... 'If an <order by clause> is not specified, then the ordering of the rows of the query is implementation-dependent.'
If the order is important then my advice is to specify it. If in doubt be explicit.
ta.
Nick
-
The only way to guarantee sort order is to specify ORDER BY. I, too, am curious as to why. If you post the problem, you may get a better answer.
-
why?
Cheers,Ben Sullins
bensullins.com
Beer is my primary key... -
Dinendra,
We need to know why you want to do this so we can give you the most correct answer.
(Thats' why, Ben
)--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
well ... as already mentioned, you have to provide an order by clause if you want data guaranteed in a certain order.
BTW Don't use the order by in a view if you also want the result ordered that way, because, it's only meanth to support the resolvance of the view, not the actual ordering of the result set. With sql2005 view containing an order by clause nolonger will be guaranteed to deliver the resultset actualy ordered that way ! The view will only filter the resultset according to the combination TOP (x) and order by.
So if you want data sorted, put an order by clause in the actual top-level query !
Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
-
You can use Select Distinct ... or Group By in special cases where you don't care if things get aggregated...
PeteK
I have CDO. It's like OCD but all the letters are in alphabetical order... as they should be. -
Because I am curious as to why ORDER BY is unacceptable to use in this case. It is intersting to me. I am not sure why you would question curiosity.
Viewing 15 posts - 1 through 15 (of 36 total)
You must be logged in to reply to this topic. Login to reply