-1

***** NOTE: IMO, this is NOT a duplicate***** There are multiple questions that show the same example query statement but those are about selecting random row. They do not explain specifically on SYNTAX of how the order by clause allows usage of a column not present in Select part, although they explain how the random generation works. If the above query were to use a function like tan(45) in the order by list it wont work. So what determines, a) which functions are allowed in the ORDER by clause? b) And why some function are added into the select list in the background and some are not.

How does SQL server allow ordering by newid(),rand() functions even when the column is not present in select list? Note: My question is NOT about how to generate random numbers.

select top 1 * from mytable order by newid()

SQL server rules specifically state:

The column names referenced in the ORDER BY clause must correspond to either a column in the select list or to a column defined in a table specified in the FROM clause without any ambiguities.

If they are an exemption, are there specific rules around that so we know how they can be used further in other scenarios?

mountainX
  • 1
  • 2
  • "The column names referenced in the ORDER BY" must.... newid() is not a column name so you can put it in the order by – kkica Oct 17 '18 at 22:52
  • You can `ORDER BY` any expression -- simple or complex -- as long it can be evaluated. Columns there don't need to be present in the resulting resultset you finally see. – The Impaler Oct 18 '18 at 00:24

1 Answers1

1

This is your query:

select top 1 t.*
from mytable
order by newid();

The order by has no reference to a column. It simply has a function call that references no columns. So, there is no problem.

By comparison, you could include newid() in the select:

select top 1 t.*, newid() as newid
from mytable
order by newid;

In this case, newid would be referencing the alias in the select. (So this would also be allowed; it is only for comparison.)

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • There is no doubt the second example would work since it is a standard format. But in example 1, even though it is a function call, internally, the newid() is being selected for each row and then ordered. So my question is, what allows SQL Server to add that function into the select list in the background? – mountainX Oct 23 '18 at 23:51
  • @mountainX Your understanding of the select clause is incorrect. While it's difficult to describe the semantics of a SQL query in an intuitive manner, since the language is declarative and select statements can be very complex, the following is a better way to think about a simple query like the above: First build a set of all the rows in the tables in the from clause, then order all the rows by generating an instance of the expression in order by for each row, this includes calling functions for each row. Finally throw away all the columns not mentioned in the select clause. – Jonas Høgh Oct 24 '18 at 18:20