Re: Create table and default order by clause

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.


No, the only way to guarantee result order is to specify ORDER BY in the SELECT statement that selects from the table.

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

No, the only way to guarantee result order is to specify ORDER BY in the SELECT statement that selects from the view. Although ORDER BY can be specified in a view along with TOP, it will not guarantee ordering.

Note that it is likely, but not guaranteed, that you will get results ordered in sequence by the table's clustered index in a trivial query with no joins or where clause. However, it's risky to rely on behavior because it may change between SQL Server service packs and releases.


--
Hope this helps.

Dan Guzman
SQL Server MVP

"D." <d@xxxxx> wrote in message news:CQz2j.6583$r62.2270@xxxxxxxxxxxxxxxxxxxxxxxx
Hi,
I have this problem.

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?

Thanks,
Davide.





.

Relevant Pages

  • Re: Using a Select Query to build a MDB table from a Fox table
    ... MS Access has a 'make-table' query with IN clause you can specify ... As you said the IN <database> clause is specific to Access SQL. ...
    (microsoft.public.fox.helpwanted)
  • Re: reordering in paralellism-multiprocessor machine
    ... As I said - no order by clause, no guarantee. ... > by the server for executing a query paralelly or serially.. ... > the execution plans from the query anaylzer. ...
    (microsoft.public.sqlserver.server)
  • Re: Order of records in table as a result of a Query
    ... A query will be displayed in the order specified. ... It is actually writing the records in the order you specify, ... Obviously the INSERT INTO is ignoring the ORDER BY clause. ... AL EXPORT in the same order as they exist in AL EXPORT SORT. ...
    (microsoft.public.access.queries)
  • Re: DISTINCT and ORDER BY
    ... Are you running this query alone with no other statements? ... Even using order by doesn't guarantee that the rows will be ... >>inserted in the order you specify. ... >>Best, Hugo ...
    (microsoft.public.sqlserver.programming)
  • Re: Doubt about using Parametrized query with IN
    ... specify a range of values which is expected with an IN clause. ... Connected Innovation ... > My query in the case is: ...
    (microsoft.public.sqlserver.ce)