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.