sort not working - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

sort not working

Author

Topic

lorna.pollard
Starting Member

5 Posts

Posted - 2007-01-29 : 03:05:09

I am having a problem with my order by when selecting data from one table to the next. Here is the code:

CREATE TABLE PSQL_ZONE ( zBASETAG nvarchar(50), zTYPE nvarchar(25), zLASTTRIP nvarchar(50), zSTATE int, zSTRAPPED int, zLAST int )
INSERT INTO PSQL_ZONE (zBASETAG, zTYPE, zSTATE, zSTRAPPED,
zLAST, zLASTTRIP)
SELECT dbo.ZONE.zBASETAG AS zBASETAG,
dbo.ZONE.zTYPE AS zTYPE,
dbo.ZONE.zSTATE AS zSTATE,
dbo.ZONE.zSTRAPPED AS zSTRAPPED,
dbo.ZONE.zLAST AS zLAST,
dbo.ZONE.zLASTTRIP AS zLASTTRIP
FROM dbo.ZONE
ORDER BY zBASETAG

If I run just the select statement in SQL Query Analyzer it returns the rows in the proper order. However, if I run my stored proc with the above code it mixes the rows up. It doesn't order by but it does change the row order.

It's 2 am so I am sure I am missing something small, but I have to install this system tomorrow and one of my processes will not work if it doesn't read the data in in order.

Thanks in advance for you help.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-01-29 : 03:28:36

try posting the Stored Procedure here

lorna.pollard
Starting Member

5 Posts

Posted - 2007-01-29 : 03:33:36

That was pretty much it...but here is the exact stored proc:

CREATE PROCEDURE ZONE_PSQL
AS

DROP TABLE PSQL_ZONE

CREATE TABLE PSQL_ZONE ( zBASETAG nvarchar(50), zTYPE nvarchar(25), zLASTTRIP nvarchar(50), zSTATE int, zSTRAPPED int, zLAST int )
INSERT INTO PSQL_ZONE (zBASETAG, zTYPE, zSTATE, zSTRAPPED, zLAST, zLASTTRIP)
SELECT dbo.ZONE.zBASETAG AS zBASETAG,
dbo.ZONE.zTYPE AS zTYPE,
dbo.ZONE.zSTATE AS zSTATE,
dbo.ZONE.zSTRAPPED AS zSTRAPPED,
dbo.ZONE.zLAST AS zLAST,
dbo.ZONE.zLASTTRIP AS zLASTTRIP
FROM dbo.ZONE
ORDER BY zBASETAG

GO

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-01-29 : 03:35:45

Some sample data would be nice!

Peter Larsson
Helsingborg, Sweden

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-01-29 : 03:37:55

quote:
However, if I run my stored proc with the above code it mixes the rows up. It doesn't order by but it does change the row order.

You mean
1. when you select * from PSQL_ZONE, the order is wrong
OR
2. the SELECT dbo.ZONE.zBASETAG AS zBASETAG, ... ORDER BY zBASETAG is wrong ?

lorna.pollard
Starting Member

5 Posts

Posted - 2007-01-29 : 03:43:09

If I run:

SELECT dbo.ZONE.zBASETAG AS zBASETAG,
dbo.ZONE.zTYPE AS zTYPE,
dbo.ZONE.zSTATE AS zSTATE,
dbo.ZONE.zSTRAPPED AS zSTRAPPED,
dbo.ZONE.zLAST AS zLAST,
dbo.ZONE.zLASTTRIP AS zLASTTRIP
FROM dbo.ZONE
ORDER BY zBASETAG

in query analyzer the sort is correct.

If I run my stored procedure the sort is not correct.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-01-29 : 03:48:50

How can you tell?
If there is a CLUSTERED INDEX on the table, the records are not shown the way you want with ORDER BY zBaseTag. It is shown according to the CLUSTERED INDEX.

Peter Larsson
Helsingborg, Sweden

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-01-29 : 03:49:40

But in the SP code you posted, you are never writing any SELECT statement to fetch the data. What you are doing is create a table and insert some data into it from another table. And data insertion can not be guaranteed to be in particular order.

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-01-29 : 03:52:14

quote:
And data ~~insertion~~ retrieval can not be guaranteed to be in particular order.

lorna.pollard
Starting Member

5 Posts

Posted - 2007-01-29 : 03:52:30

So how can I get the data from one table into another in a particular order?

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-01-29 : 03:53:50

quote:
It's 2 am

Go and sleep.

First thing you wake up later is run this


select * from PSQL_ZONE order by zBASETAG

And you see than everything is in order

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-01-29 : 03:54:18

[code]CREATE PROCEDURE ZONE_PSQL
AS

DROP TABLE PSQL_ZONE

CREATE TABLE PSQL_ZONE
(
zBASETAG nvarchar(50),
zTYPE nvarchar(25),
zLASTTRIP nvarchar(50),
zSTATE int,
zSTRAPPED int,
zLAST int
)

CREATE CLUSTERED INDEX IX_PSQL_ZONE ON PSQL_ZONE (zBASETAG)

INSERT PSQL_ZONE
(
zBASETAG,
zTYPE,
zSTATE,
zSTRAPPED,
zLAST,
zLASTTRIP
)
SELECT zBASETAG,
zTYPE,
zSTATE,
zSTRAPPED,
zLAST,
zLASTTRIP
FROM dbo.ZONE[/code]

Peter Larsson
Helsingborg, Sweden

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-01-29 : 03:55:58

quote:
Originally posted by lorna.pollard

So how can I get the data from one table into another in a particular order?

NOT HOW to get INTO one table but HOW to get FROM one table.
use the ORDER BY clause.

As Peter highlighted, without ORDER BY clause, the order of record in result is dependent to index.

lorna.pollard
Starting Member

5 Posts

Posted - 2007-01-29 : 04:05:26

Thanks Peso

CREATE CLUSTERED INDEX IX_PSQL_ZONE ON PSQL_ZONE (zBASETAG)

did the trick....

I can finally get a few hours of sleep before I have to get this system up and running. The SQL server is attached to a SCADA system and getting that data in the right order is crucial.

Thanks so much!

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-01-29 : 04:15:09

GETTING the data, is equivalent to SELECT!
The INSERT together with ORDER BY is of no meaning unless you also have a IDENTITY column in the target table.

Peter Larsson
Helsingborg, Sweden

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-01-29 : 04:15:43

Or you have some kind of obscure CURSOR later to calculate some values.

Peter Larsson
Helsingborg, Sweden

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-01-29 : 04:17:16

your table does not has any identity column. So getting data in the right order IN not important. It should be getting data OUT in the right order that is crucial.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources