TOP, ORDER BY, and Non-Unique Columns

  • Article

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