TOP, ORDER BY, and Non-Unique Columns


One of the comments I accidentally deleted earlier this afternoon posed the following question (paraphrased):



I understand that TOP with ORDER BY makes no sense, but what about when I use different values for TOP and I ORDER BY a column that has duplicate values?  I don’t get the same results every time.


This is a case where the query as written is not deterministic (which is just a fancy way of saying the same thing the commenter did — we don’t get the same results every time).  If we have a table Test where the integer column Col1 is non-negative and we have 15 records where Col1 = 0, then this query..


SELECT TOP 10 * FROM Test ORDER BY Col1


will not reliably return the same 10 records on every execution.  The only way to get a deterministic query using TOP and ORDER BY is to ORDER BY a unique construct.  In the scenario posed in the comment, I can see two ways to do this:



  • add enough columns to the ORDER BY construct to guarantee uniqueness (e.g., ORDER BY Col1, Col2, Col3)

  • ORDER BY a different, unique column (e.g., ORDER BY UniqueCol1)

Thanks, commenter, for your question, and apologies for its erroneous deletion.



-wp

Comments (1)

  1. SQL Server Faqs says:

    I think to make sense of query; ORDER BY column should have unique values. If not You should use multiple columns in ORDER BY to ensure uniqueness as a combine column.

    <a href="http://www.techbaba.com/faqs/sql+server+question+answers.aspx"&gt; SQL Server Question Answers</a>