Re: Create table and default order by clause

D. (d@xxxxx) writes:
My software creates a temporary table (#MyTable).
This table should be used by a report engine and printed each time with
different "order by" clause, depending on some parameters (and the program
that creates the temporary table obviously knows these parameters...)

Now, I don't want to pass these paramete to the report engine, because
I want that the logic of the report will stay only in the program that
create the table (the report engine should onnly do a "SELECT * FROM
#MyTable").

So, I'm asking if there is a way to define, for a table, a default
"order by" clause to use when no "order by" clause is specified in a
"select" query statement on that table.

If not, I think the only alternative is to create a view on that table. Is
it correct?

As the others have said: the only way to be guaranteed to get an ordered
result is to use ORDER BY. There is no way around that.

However, you could create your table with a rowno column which you
populate with the row_number() function (available from SQL 2005), and
the report engine could do "SELECT ... FROM #MyTable ORDER BY rowno".


--
Erland Sommarskog, SQL Server MVP, esquel@xxxxxxxxxxxxx

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
.