Order by with select into

I have a Microsoft SQL Server 7.0.

I wrote a sql command that creates a temporary table with a ORDER BY
clause.

When a execute a SELECT on this temporary table sometimes the result is
ok, but sometimes is not ordered. I didnt see anything like that. Any
clue?

Is there any kind of limits with temporary tables ? Because the command
that creates the temporary table is working and the rsults is always
ordered. But when I create a table with it, sometimes the table is not
ordered.

Paulo

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!"Paulo Andre Ortega Ribeiro" <anonymous@.devdex.com> wrote in message
news:3f0b0ac2$0$202$75868355@.news.frii.net...
> I have a Microsoft SQL Server 7.0.
> I wrote a sql command that creates a temporary table with a ORDER BY
> clause.
> When a execute a SELECT on this temporary table sometimes the result is
> ok, but sometimes is not ordered. I didnt see anything like that. Any
> clue?
> Is there any kind of limits with temporary tables ? Because the command
> that creates the temporary table is working and the rsults is always
> ordered. But when I create a table with it, sometimes the table is not
> ordered.
> Paulo

Rows in tables (temporary or permanent) never have an order, even if you
used ORDER BY when you did the INSERT, and even if there's a clustered index
on the table. The only way to be sure you get ordered data is to use ORDER
BY when you SELECT it.

Some tables, especially with clustered indexes, may look like the data is
ordered, but you can't assume it will always work. You could think of it
like this - when you SELECT from the table, you create a result set, and
ORDER BY only works on the result set, not on the table.

Is there some specific reason that you want to order data in the table,
instead of using ORDER BY in your queries? If there is, then maybe you could
give some more details about what you are trying to do, and someone might be
able to suggest a different solution.

Simon|||Paulo,

Tables do not have an order. In other words, tables by definition are
logically an unordered set of rows. Using ORDER BY in a INSERT...SELECT or
SELECT...INTO does not mean that the data in the table is 'ordered'. The
order of rows which you see when you do a SELECT without an ORDER BY clause
is a undefined/arbitrary order chosen by the optimizer based on the physical
characteristics, indexes, access paths, complexity of joins if any, other
statistical information etc. & many undocumented factors. Hence you should
not rely on the 'order' of rows in a table which you see on the screen.

To repeat, rows in a table do not have a logical order. The only guaranteed
way of retrieving rows in a specific order is to use an ORDER BY clause in
your SELECT statement.

--
- Anith
( Please reply to newsgroups only )sql

Order by Issue

I am having a small issue with the order by command.

Everytime I run the code below, everything displays in the correct order which was assigned by the order by command:

select *
from [table1]
order by column_a, column_b, column_c

My problem is that I want it to be stored in another table in the order that I specify, so I run the code below which has the same order:

select *
into [new table]
from [table1]
order by column_a, column_b, column_c

My problem is that when I do a select * form the new table that I inserted all the information into in order, it will not be returned in order. Why would that be? My understanding is that it would store the info in the new table by the way I specified on the order by line.

Any help would be appreciated. It is driving me crazy!Data in a recordset has no order unless specified. While you may have specified an order when you inserted the records, there is no guarantee that they will be maintained in that order, or returned in that order.

You can ensure that they are stored in a specific order by placing a clustered index on the columns you want, and MOST of the time queries against the table will return the records in that order, but again there is no guarantee.

You should always issue an ORDER BY clause on all SQL Statements which require data to be returned in a specified order.|||The only time the resultset will be in the order of the clustered index is if you run your SELECT on a single processor machine, or if you set only 1 processor for SQL Server service, or if you use OPTION (MAXDOP 1). In any other case the order of the clustered index does not affect the order of the resultset without ORDER BY.

ORDER BY Command in SQL Stored Procedure

How do you use a variable in the ORDER BY command in a sql statement.
I currently have:
****************************************
CREATE Procedure SS_POList
(
@.CompanyID nvarchar(10),
@.PO varchar (20) = '%'
)
As

SELECT
SS_Sendback.EndUserPO,
SS_Sendback.PO,
COUNT(SS_Sendback.EndUserPO) as pocount,
SUM(SS_Sendback.Customerprice) as totcost

FROM
SS_Sendback

WHERE
SS_Sendback.EndusercustomerNumber = @.CompanyID
AND
SS_Sendback.EnduserPO Like @.PO

GROUP BY
SS_Sendback.EndUserPO,
SS_Sendback.PO

ORDER BY
SS_Sendback.PO
GO
*************************************
I changed it to
*************************************
CREATE Procedure SS_POList
(
@.CompanyID nvarchar(10),
@.PO varchar (20) = '%',
@.Order varchar(20)
)
As

SELECT
SS_Sendback.EndUserPO,
SS_Sendback.PO,
COUNT(SS_Sendback.EndUserPO) as pocount,
SUM(SS_Sendback.Customerprice) as totcost

FROM
SS_Sendback

WHERE
SS_Sendback.EndusercustomerNumber = @.CompanyID
AND
SS_Sendback.EnduserPO Like @.PO

GROUP BY
SS_Sendback.EndUserPO,
SS_Sendback.PO

ORDER BY
@.Order
GO

and I receive the following error..
error1008: the select item identified by the Order By number 1 contains a variable as part of the expression identifying a column position. Variables are only allowed when ordering by an expression referencing a column name.
How does it know @.order is a position and not a column name... It's a variable. I'm obviously doing something wrong. Can someone help.
Thanksas you've discovered, the order by cannot be dynamic. you'd have to use a CASE statement to use a variable here - pseudocode

CASE @.orderBy = 'column1'
ORDER BY column1

that kinda thing. I've seen an FAQ on this somewhere, I'll see if I can dig out the link.|||I'd like to see the FAQ if you can find it. I tried looking but I had no luck.|||try this one for size :

http://www.eggheadcafe.com/PrintSearchContent.asp?LINKID=344|||Rather than the method suggested in the article:

declare @.OrderBy varchar(20)
select @.Orderby = 'ContactName'
select * from Northwind.dbo.Customers
ORDER BY
 CASE @.OrderBy
 WHEN 'CompanyName' THEN CompanyName
 WHEN 'ContactName' THEN ContactName
 END
 ASC

I suggest this method:

declare @.OrderBy varchar(20)
select @.Orderby = 'ContactName'
select * from Northwind.dbo.Customers
ORDER BY
 CASE WHEN @.OrderBy ='CompanyName' THEN CompanyName END ASC,
 CASE WHEN @.OrderBy ='ContactName' THEN ContactName END ASC

The method in the article requires all of the columns to be of the same datatype (or CAST as the same datatype). The alternate method allows you to use different data types easily, plus use ASC and DESC as needed.

Terri|||Thanks for all you help.
This seems like a lot of work when a simple
ORDER BY @.somevariable
should work but apperently thats not supported by SQL server.
In my searches I also found some suggestions to assign the search string to a variable and execute the string but I think that would be the least desireable way to do this.
Thanks Again.

oracle pl/sql multiple spool help required

Now Im not an expert or anything with SQL so bear with me...

I have 3 seperate queries that each use the spool command to write to a file on the server.
e.g. i use the following construct in each of the three sql files:

set blah
set blah
spool /blah/blah.rpt
script
spool off

I wish to put these three queries into one script. Can I use the spool command three times in the one file? i.e. have spool then spool off, three times in one *.sql file?

I have read a couple of posts on pl/sql spooling and people mention utl_file, but I have no idea what this is and even if i have it...

Any help would be great. Thanks.Yes, you can. For example,

spool a1.txt
select count(*) from tab;
spool off;

spool a2.txt
select sysdate from dual;
spool off;

spool a3.txt
select 'x' dummy from dual;
spool off;

will generate 3 .txt files. However, I can't figure out why didn't you try it yourself ...|||I did try it and it didnt work the way I expected...

I have the three spools and also I have a title for each of the three files:

e.g.
ttitle 'Thanet ** Items made MISSING between 7 and 14 days ago' skip 2

I have one of these fore each script [different txt of course]. I have found that if the query returns no results it will not print the title to the file and so i have an emty file. If there are some results from on of the queries then the title does display.

I was wanting to make sure that I was using the spool correctly so thats why i asked.|||Perhaps this helps ... if you include a "dummy" query into every "spool block", you won't get an empty file even though your "real" query returns no records.

SPOOL a1.txt
TTITLE 'First top title' skip 2
BTITLE 'First bottom title'
COLUMN dummy noprint;
SELECT 'x' dummy FROM DUAL;

SELECT COUNT (*) FROM tab;
SPOOL off;

SPOOL a2.txt
TTITLE 'Second top title' skip 2
BTITLE 'Second bottom title'
COLUMN dummy noprint;
SELECT 'x' dummy FROM DUAL;

-- this query returns no rows
SELECT 'x' FROM dual WHERE sysdate = sysdate + 1;
SPOOL off;|||thanks for the help, ill give that a try.