Sorting Problem

Question

Sorting Problem

SSChasing Mays

Points: 632

June 30, 2004 at 4:32 am

Hi Everybody,I am having a strange problem, i wonder if anybody knows the solution:

I have written the following SP:

CREATE   PROCEDURE sp_search_results

@SID varchar(50),

@STypeID int,

AS

SELECT field1,field2,field3  INTO #tempsearch  FROM tbl_cfields ORDER BY  field1

insert into STable (SID, STypeID, counter, cCode, cdesc)

select @SID, @STypeID, 0, field1, field2

from #tempsearch order by field1

update STable

set counter = @counter,

@counter = @counter + 1

where sid=@sid

and STypeID=@STypeID

But when i execute this query:

select counter,   CCode,   field1,   field2   FROM STable

  WHERE sid=@sid   and stypeid=6

order by counter

The result set aways has a different order. The problem occurs in the insert into statement,

 it does not insert the data in sorted order inspite of order by clause.

i Have created STable with the following script.

CREATE TABLE STable ( SID varchar (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,

 STypeID int NOT NULL ,

Counter int NOT NULL ,

 IntID int NULL ,

CCode char (256) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

Cdesc [varchar] (256) COLLATE SQL_Latin1_General_CP1_CI_AS NULL)

 ON PRIMARY

I have even created STable without specifying the collations like this:

CREATE TABLE STable ( SID varchar (50)  NOT NULL ,

 STypeID int NOT NULL ,

 Counter int NOT NULL ,

 IntID int NULL ,

 CCode char (256)  NULL ,

Cdesc [varchar] (256)  NULL)

 ON PRIMARY

but it doesn't work.

However when i use a temporary table instead of Permanent table it works fine

and the data is always sorted, the code is like this:

CREATE   PROCEDURE sp_search_results

@SID varchar(50),

@STypeID int

AS

SELECT field1,field2,field3  INTO #tempsearch  FROM tbl_cfields ORDER BY  field1

insert into #tempSTable (SID, STypeID, counter, cCode, cdesc)

select @SID, @STypeID, 0, field1, field2

from #tempsearch order by field1

update #tempSTable

set counter = @counter,

@counter = @counter + 1

where sid=@sid

and STypeID=@STypeID

insert into STable (SID, STypeID, counter, cCode, cdesc)

select SID, STypeID, counter, cCode, cdesc

from #tempSTable

I presume that it has something to do with collations,

but tempdb and the database that i have has same collation settings.

Can anybody tell me the reason?

Here is the output of the query after different execution of the sp. on the same data.

counter     CCode          field1          field2

----------- -------------- ------------- ------------------------

1           ACETRAVE0001   ACETRAVE0001    Ace Travel

2           ADVANCED0001   ADVANCED0001    Advanced Office Systems

3           ALLENSON0001   ALLENSON0001    Allenson Properties

4           AMERICAN0001   AMERICAN0001    AmericaCharge

5           ASSOCIAT0001   ASSOCIAT0001    Associated Insurance Inc.

6           ATTRACTI00001  ATTRACTI00001   Attractive Telephone Co.

counter     CCode            field1         field2

----------- -------------- --------------- -----------------------------------------------------------------

1           PAGEMAST0001  PAGEMAST0001    PageMaster

2           PERMIERS0001  PERMIERS0001    Premier System, Inc.

3           PRINTER1      PRINTER1        Print Vendor

4           PROFESSI0001  PROFESSI0001    Professional Travel Consultant

5           READYREN0001  READYREN0001    Ready Rentals

Thanks

nomi

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

MVDBA (Mike Vessey) SSC-Insane Points: 21797 More actions · Answer 1

where did you decalare the @counter variable used in the following? unless i'm being really stupid here.

update STable

set counter = @counter,

@counter = @counter + 1

where sid=@sid

and STypeID=@STypeID

have you also though of using clustered indexes in your table definitions in so that the data is physically stored by Field1 and the use of order by clauses becomes unnecessary

MVDBA

Frank Kalis SSC Guru Points: 111183 More actions · Answer 2

Mike,

you can use HTML tags even in your autosignature!

Makes it easier for people to visit your website

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

MVDBA (Mike Vessey) SSC-Insane Points: 21797 More actions · Answer 3

True.

Just waiting to sort out all the other stuff as well (like a company logo etc...)

just for you i'll sort it out today!

MVDBA

Frank Kalis SSC Guru Points: 111183 More actions · Answer 4

That's very good of you

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

MVDBA (Mike Vessey) SSC-Insane Points: 21797 More actions · Answer 5

MVDBA (Mike Vessey)

SSC-Insane

Points: 21797

June 30, 2004 at 6:33 am

#512637

et voila.

my logo and hyperlink!!

MVDBA

Frank Kalis SSC Guru Points: 111183 More actions · Answer 6

Cute!

I like this HTML-thingy

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

nomi khan SSChasing Mays Points: 632 More actions · Answer 7

Yes i forgot to do that but u can declate it and initia;lize it with 0. But it doesn't affect the output.

Thanks

nomi