insert - select not ordering data properly
-
I'm doing some paging of data in my stored procedure however when the records are inserted into the temp table (which has an identity column) they're not inserted according to the ORDER BY clause in the SELECT statement. I'm done this numerous times before but this time the SELECT statement is quite a bit more complex than otheres I've done and was wondering if the complexities may be running into a bug or something. The SELECT statement, when run alone, returns the records in the proper order, however as soon as I try to insert them in the table the are in a completely different order.
I can post the query if necessary but it's a little messy.
Anyone have any info on possible problems with INSERT-SELECT not ordering properly?
-
Does the temp table you're inserting into have an index on it? When you're selecting the records from the temp table, are you using an ORDER BY.
Any chance of posting your query and table definition?
--------------------
Colt 45 - the original point and click interface
-
There is no such thing as a specific order in a set of tuples (which is more or less what a table represents). If you need the data to be ordered in a specific way when read you must use ORDER BY when reading it.
-
The select statement that is used for the insert has an order by on it. Here's the temp table definition and the insert-select statement:
CREATE TABLE #tmp (rownum int identity(1,1) not null primary key,[ItemID] [int] NOT NULL ,[ItemGUID] [uniqueidentifier] NOT NULL ,[ParentTopicID] [int] NULL ,[OwningUserID] [int] NULL ,[Name] [varchar] (250) NOT NULL ,[SEName] [varchar] (250) NULL ,[ShortName] [varchar] (35) NOT NULL ,[AboutDisabled] [bit] NOT NULL ,[DisplayOrder] [int] NOT NULL ,[Description] [ntext] NULL ,[ShopDisabled] [tinyint] NOT NULL ,[ShopNoCaption] [tinyint] NULL ,[GeneralShopDisabled] [tinyint] NOT NULL ,[RelatedSitesDisabled] [tinyint] NOT NULL ,[IsDisabled] [tinyint] NOT NULL ,[CreatedOn] [datetime] NOT NULL ,[VisitURL] [ntext] NULL ,[VisitPrompt] [varchar] (50) NULL ,[ShopURL] [ntext] NULL ,[Address1] [varchar] (255) NULL ,[Address2] [varchar] (255) NULL ,[Suite] [varchar] (100) NULL ,[City] [varchar] (255) NULL ,[State] [varchar] (100) NULL ,[ZipCode] [varchar] (10) NULL ,[Country] [varchar] (100) NULL ,[Phone] [varchar] (50) NULL ,[FAX] [varchar] (50) NULL , [ntext] NULL ,[Email] [varchar] (100) NULL ,[OvertureKeywords] [ntext] NULL ,[SE_Title] [ntext] NULL ,[SE_Description] [ntext] NULL ,[SE_Keywords] [ntext] NULL ,[ExtendedData] [ntext] NULL ,[SubmittedByUserID] [int] NULL, [NumRatings] [int] not null, [AvgRating] [decimal](6, 5) not null, [NumComments] [int] not null, [UserRating] [decimal](3,2) not null, [AlertUser] [int] not null)
INSERT #tmp (ItemID, ItemGUID, ParentTopicID, OwningUserID, Name, SEName, ShortName, AboutDisabled, DisplayOrder, Description, ShopDisabled, ShopNoCaption, GeneralShopDisabled, RelatedSitesDisabled, IsDisabled, CreatedOn, VisitURL, VisitPrompt, ShopURL, Address1, Address2, Suite, City, State, ZipCode, Country, Phone, FAX, URL, Email, OvertureKeywords, SE_Title, SE_Description, SE_Keywords, ExtendedData, SubmittedByUserID, NumRatings, AvgRating, NumComments, UserRating, AlertUser)
SELECT i.*, isnull(a.NumRatings,0) NumRatings, isnull(a.AvgRating, 0) AvgRating, isnull(a.NumComments, 0) NumComments, isnull(r.rating, 0), isnull(al.alertOwnerUserID, -1)
FROM Item i (nolock) join
(select ir.itemid, count(ir.rating) NumRatings, avg(ir.rating*1.0) AvgRating, sum(ir.HasComment) NumComments
from ItemRatings ir with (nolock)
where ir.TopicID=@topicid
and ir.userAge between @lowAge and @highAge
and ir.userGender = coalesce(@gender, ir.userGender)
group by ir.itemid) a on i.itemid = a.itemid
left join ItemRatings r on i.itemid = r.itemid and r.userid = @userid
left join Alerts al on i.itemid = al.itemid and al.alertOwnerUserID = @userid and alertType = 1
WHERE i.IsDisabled = 0
and isnull(i.ZipCode, '') = coalesce(@zipcode, isnull(i.ZipCode, ''))
ORDER BY isnull(a.AvgRating, 0) desc, isnull(a.NumRatings,0) desc, i.Name asc
-
I once had the similar issue, though not identical. I had SET ROWCOUNT n statmement before a INSERT SELECT ORDER BY command. The data inserted into the table is not in the order on one machine. But the SP in same database on all other machine worked fine.
Later we changed to use dynamic SQL and TOP n and solved the issue.
-
So what is the result for
SELECT #tmp.* FROM #Tmp ORDER BY AvgRating desc, NumRatings desc, Name asc
--------------------
Colt 45 - the original point and click interface -
I don't order the select from teh temp table that way since I'm using the temp table to select a page.
EX:
SELECT *
FROM #tmp
WHERE rownum >= @pagesize*(@pagenum-1)+1 and rownum <= @pagesize*(@pagenum)
ORDER BY rownum
The problem is that the records are not being inserted such that the first record (according to the order by on the select clause) is not the record where rownum=1 and so forth. When I execute the SELECT statment by itself the records are return in the proper order (i.e. the order specified by the ORDER BY clause). When I try use that select statement to insert them into the temp table they are insert in some random order.
I have this wroking in many other procs but for some reason this one is not working.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply