andrew.ing (9/30/2014)
Todd Reddinger (9/30/2014)
the ORDER BY in the OVER clause orders the records returned by the select. By adding an order by on the select, a differect set of records is returned for the ORDER BY in the OVER clause to order.SELECT TOP 20
RAND(RANK() OVER ( ORDER BY id ))
FROM
sys.syscolumns AS s order by s.colid
In practice the OVER clause often does order the rows returned, in the above it is the only sorting that is done. But is that guaranteed? What would happen if there were two OVER clauses:
SELECT
id,
RANK() OVER (ORDER BY id ASC) AS RankAsc
,RANK() OVER (ORDER BY id DESC) AS RankDesc
FROM
sys.syscolumns AS s
Would the first or the second OVER BY clause define the SELECT order? On my test, id is sorted in descending order.
I'd say that the ordering is a side effect of calculating the RANK function, and that the order of the SELECT is undefined without an ORDER BY clause on the SELECT itself.
Andrew you are absolutely correct. The actual output has no defined order. However, the RANK function is what is defining the seed for the RAND function. It doesn't actually matter what order the results are, the question didn't ask anything about what order they are in. It asked how many distinct values would be returned.
I thought it was an interesting question but not thought out completely. I figured the least incorrect choice was 2 so went with that and got lucky.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/