how does order by work when values are the same

Question

how does order by work when values are the same

gorenak

Old Hand

Points: 394
More actions
February 2, 2012 at 6:13 am

#252295

hi
I put this under newbies, because maybe it is.
I have table users and rank like this
userid, name, email, RANK, lastVisit, pictures, and some more.
my select is:
select ContributorID, FirstName, email, Rank
from dbo.Contributor
order by rank desc
And because rank is only 1 or 2(at this time) and users are more, I get some data.
My questing is, by witch parameter inside order MS SQL server display data?
random or by what?

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

SQLRNNR SSC Guru Points: 281334 More actions · Answer 1

If the values are the same in the column used in the order by, the results will be random. You may see the same order of the results after several runs and then it will suddenly change. Try adding another column to the order by to get more consistent results.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

Paul White SSC Guru Points: 150468 More actions · Answer 2

gorenak (2/2/2012)
My questing is, by witch parameter inside order MS SQL server display data?

It's not defined, because it depends on internal implementation details. So, it's not random exactly, but you can't depend on it being repeatable.

Paul White
All articles available on SQL.kiwi
@SQL_Kiwi

gorenak Old Hand Points: 394 More actions · Answer 3

ok but why this code

DECLARE @i INT

DECLARE @j-2 INT

SET @i = 1

SET @j-2 = 1

WHILE @j-2 < 100000

BEGIN

SET @i = (SELECT TOP 1 ContributorID

FROM dbo.Contributor

ORDER BY RANK DESC)

SET @j-2 = @j-2 + 1

IF @i <> 68 PRINT 'random works'

END in 10 0000 repeats never change?

Paul White SSC Guru Points: 150468 More actions · Answer 4

gorenak (2/2/2012)
ok but why this code in 10 0000 repeats never change?

Because it isn't random, it depends on code paths within SQL Server which none of us can see. You might observe repeatable results, but you cannot rely on it. It's just like expecting ordered results from a clustered index scan - it's easy to show results often come out in the expected order, but SQL Server does not make any guarantee without an explicit ORDER BY, so it would be foolish to depend on it. The same argument applies to your test - observed behaviour != safe to rely on.

Paul White
All articles available on SQL.kiwi
@SQL_Kiwi

Grant Fritchey SSC Guru Points: 398909 More actions · Answer 5

Probably just has to do with the structure and access mechanisms. Assuming no changes to the execution plan, no changes to the distribution of the data, no changes to the optimizer, you'll probably get repeatable results. But change any of those things and the results can change. Again, I wouldn't count that as reliable behavior.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

gorenak Old Hand Points: 394 More actions · Answer 6

ok thanks for answer, because I suspect that it is not random 😀

Paul White SSC Guru Points: 150468 More actions · Answer 7

gorenak (2/2/2012)
ok thanks for answer, because I suspect that it is not random 😀

So long as you understand why you can't rely on it, that's cool.

Paul White
All articles available on SQL.kiwi
@SQL_Kiwi