Ward Pond's SQL Server blog

Ruminating on issues pertinent to the design and development of sound databases and processes under Microsoft SQL Server 2008, SQL Server 2005, and SQL Server 2000 (while reserving the right to vent about anything else that's on my mind)

TOP, ORDER BY, and Non-Unique Columns

TOP, ORDER BY, and Non-Unique Columns

  • Comments 1
  • Likes

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
Your comment has been posted.   Close
Thank you, your comment requires moderation so it may take a while to appear.   Close
Leave a Comment