|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
ordered insert - select is not inserting in orderclient. However, the data is not inserted in the order that is specified in the query. The data is not in the table in the order that the ORDER BY clause specifies. I do this in other procs without a problem and even if I just execute the select statement the result is ordered as specified. Why won't it insert into the table properly ordered. Here's the whole messy thing: CREATE TABLE #tmp (rownum int not null identity(1,1) 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 , [URL] [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 = 90 and ir.userAge between 0 and 255 and ir.userGender = coalesce(null, ir.userGender) group by ir.itemid) a on i.itemid = a.itemid left join ItemRatings r on i.itemid = r.itemid and r.userid = 20069715 left join Alerts al on i.itemid = al.itemid and al.alertOwnerUserID = 20069715 and alertType = 1 WHERE i.IsDisabled = 0 and isnull(i.ZipCode, '') = coalesce(null, isnull(i.ZipCode, '')) ORDER BY a.AvgRating desc, a.NumRatings desc, i.Name asc If you dont apply a clustered index on the columns you want to have the
table sorted you will get unexpected results. A Database is not there to store data in a predefined order, thats what the query processor is for, to get them back with the syntax of an ORDER BY. HTH, Jens. Show quote "Buddy Ackerman" <buddy_nospam@buddyackerman.com> wrote in message news:O7osgWKtFHA.1848@TK2MSFTNGP10.phx.gbl... > I'm inserting into a temp table with the intent of ordering the data so > that I can select a "page" of it to send to the client. However, the data > is not inserted in the order that is specified in the query. > > The data is not in the table in the order that the ORDER BY clause > specifies. I do this in other procs without a problem and even if I just > execute the select statement the result is ordered as specified. Why > won't it insert into the table properly ordered. > > > Here's the whole messy thing: > > > CREATE TABLE #tmp (rownum int not null identity(1,1) 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 , [URL] [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 = 90 > and ir.userAge between 0 and 255 > and ir.userGender = coalesce(null, ir.userGender) > group by ir.itemid) a on i.itemid = a.itemid > left join ItemRatings r on i.itemid = r.itemid and r.userid = > 20069715 > left join Alerts al on i.itemid = al.itemid and > al.alertOwnerUserID = 20069715 and alertType = 1 > WHERE i.IsDisabled = 0 > and isnull(i.ZipCode, '') = coalesce(null, isnull(i.ZipCode, '')) > ORDER BY a.AvgRating desc, a.NumRatings desc, i.Name asc How do you know it is not being inserted in the order specified by the "order
by" clause? AMB Show quote "Buddy Ackerman" wrote: > I'm inserting into a temp table with the intent of ordering the data so that I can select a "page" of it to send to the > client. However, the data is not inserted in the order that is specified in the query. > > The data is not in the table in the order that the ORDER BY clause specifies. I do this in other procs without a > problem and even if I just execute the select statement the result is ordered as specified. Why won't it insert into > the table properly ordered. > > > Here's the whole messy thing: > > > CREATE TABLE #tmp (rownum int not null identity(1,1) 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 , [URL] [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 = 90 > and ir.userAge between 0 and 255 > and ir.userGender = coalesce(null, ir.userGender) > group by ir.itemid) a on i.itemid = a.itemid > left join ItemRatings r on i.itemid = r.itemid and r.userid = 20069715 > left join Alerts al on i.itemid = al.itemid and al.alertOwnerUserID = 20069715 and alertType = 1 > WHERE i.IsDisabled = 0 > and isnull(i.ZipCode, '') = coalesce(null, isnull(i.ZipCode, '')) > ORDER BY a.AvgRating desc, a.NumRatings desc, i.Name asc > Because I select it using the following query.
SELECT * FROM #tmp WHERE rownum >= @pagesize*(@pagenum-1)+1 and rownum <= @pagesize*(@pagenum) Alejandro Mesa wrote: Show quote > How do you know it is not being inserted in the order specified by the "order > by" clause? > > > AMB > > "Buddy Ackerman" wrote: > > >>I'm inserting into a temp table with the intent of ordering the data so that I can select a "page" of it to send to the >>client. However, the data is not inserted in the order that is specified in the query. >> >>The data is not in the table in the order that the ORDER BY clause specifies. I do this in other procs without a >>problem and even if I just execute the select statement the result is ordered as specified. Why won't it insert into >>the table properly ordered. >> >> >>Here's the whole messy thing: >> >> >>CREATE TABLE #tmp (rownum int not null identity(1,1) 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 , [URL] [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 = 90 >> and ir.userAge between 0 and 255 >> and ir.userGender = coalesce(null, ir.userGender) >> group by ir.itemid) a on i.itemid = a.itemid >> left join ItemRatings r on i.itemid = r.itemid and r.userid = 20069715 >> left join Alerts al on i.itemid = al.itemid and al.alertOwnerUserID = 20069715 and alertType = 1 >> WHERE i.IsDisabled = 0 >> and isnull(i.ZipCode, '') = coalesce(null, isnull(i.ZipCode, '')) >> ORDER BY a.AvgRating desc, a.NumRatings desc, i.Name asc >> Buddy Ackerman,
SQL Server does not asure any order of the rows returned by a "select" statement if the clause "order by" is not used. In case you are trying to number the rows returned by the select statement, here are a couple of links that can guide you. How to dynamically number rows in a SELECT Statement http://support.microsoft.com/default.aspx?scid=kb;en-us;186133 How do I return row numbers with my query? http://www.aspfaq.com/show.asp?id=2427 AMB Show quote "Buddy Ackerman" wrote: > Because I select it using the following query. > > > SELECT * > FROM #tmp > WHERE rownum >= @pagesize*(@pagenum-1)+1 and rownum <= @pagesize*(@pagenum) > > > > > > Alejandro Mesa wrote: > > How do you know it is not being inserted in the order specified by the "order > > by" clause? > > > > > > AMB > > > > "Buddy Ackerman" wrote: > > > > > >>I'm inserting into a temp table with the intent of ordering the data so that I can select a "page" of it to send to the > >>client. However, the data is not inserted in the order that is specified in the query. > >> > >>The data is not in the table in the order that the ORDER BY clause specifies. I do this in other procs without a > >>problem and even if I just execute the select statement the result is ordered as specified. Why won't it insert into > >>the table properly ordered. > >> > >> > >>Here's the whole messy thing: > >> > >> > >>CREATE TABLE #tmp (rownum int not null identity(1,1) 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 , [URL] [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 = 90 > >> and ir.userAge between 0 and 255 > >> and ir.userGender = coalesce(null, ir.userGender) > >> group by ir.itemid) a on i.itemid = a.itemid > >> left join ItemRatings r on i.itemid = r.itemid and r.userid = 20069715 > >> left join Alerts al on i.itemid = al.itemid and al.alertOwnerUserID = 20069715 and alertType = 1 > >> WHERE i.IsDisabled = 0 > >> and isnull(i.ZipCode, '') = coalesce(null, isnull(i.ZipCode, '')) > >> ORDER BY a.AvgRating desc, a.NumRatings desc, i.Name asc > >> > Quite trying to answer a question you clearly don't understand. The select statement used to insert into the temporary
talbe has an ORDER BY clause on it. This a technique commonly used to get paged data, it works in many other stored procedures that I have written. This time however the rows are not being inserted into my temporary table in the proper order (as specified by the ORDER BY clause that is plainly there that apparently you cannot see). Alejandro Mesa wrote: Show quote > Buddy Ackerman, > > SQL Server does not asure any order of the rows returned by a "select" > statement if the clause "order by" is not used. > > In case you are trying to number the rows returned by the select statement, > here are a couple of links that can guide you. > > How to dynamically number rows in a SELECT Statement > http://support.microsoft.com/default.aspx?scid=kb;en-us;186133 > > How do I return row numbers with my query? > http://www.aspfaq.com/show.asp?id=2427 > > > AMB > > > > "Buddy Ackerman" wrote: > > >>Because I select it using the following query. >> >> >> SELECT * >> FROM #tmp >> WHERE rownum >= @pagesize*(@pagenum-1)+1 and rownum <= @pagesize*(@pagenum) >> >> >> >> >> >>Alejandro Mesa wrote: >> >>>How do you know it is not being inserted in the order specified by the "order >>>by" clause? >>> >>> >>>AMB >>> >>>"Buddy Ackerman" wrote: >>> >>> >>> >>>>I'm inserting into a temp table with the intent of ordering the data so that I can select a "page" of it to send to the >>>>client. However, the data is not inserted in the order that is specified in the query. >>>> >>>>The data is not in the table in the order that the ORDER BY clause specifies. I do this in other procs without a >>>>problem and even if I just execute the select statement the result is ordered as specified. Why won't it insert into >>>>the table properly ordered. >>>> >>>> >>>>Here's the whole messy thing: >>>> >>>> >>>>CREATE TABLE #tmp (rownum int not null identity(1,1) 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 , [URL] [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 = 90 >>>> and ir.userAge between 0 and 255 >>>> and ir.userGender = coalesce(null, ir.userGender) >>>> group by ir.itemid) a on i.itemid = a.itemid >>>> left join ItemRatings r on i.itemid = r.itemid and r.userid = 20069715 >>>> left join Alerts al on i.itemid = al.itemid and al.alertOwnerUserID = 20069715 and alertType = 1 >>>> WHERE i.IsDisabled = 0 >>>> and isnull(i.ZipCode, '') = coalesce(null, isnull(i.ZipCode, '')) >>>> ORDER BY a.AvgRating desc, a.NumRatings desc, i.Name asc >>>> >> The select statement you issue to retrieve the rows doesn't have an order by
clause, however. When you issue the select statement, are the rownumbers returned in order? Can you post DDL and sample data? Can you post the execution plan for the insert statement? It is extremely hard to provide assistance without DDL and sample data. Although, after blasting Alejandro Mesa like you did, I don't think many on this forum would be so inclined. Of course, you can always pay Microsoft for help. I think the going rate is $300 per incident. "Buddy Ackerman" <buddy_nospam@buddyackerman.com> wrote in message statement used to insert into the temporarynews:ual4UMMtFHA.256@tk2msftngp13.phx.gbl... > Quite trying to answer a question you clearly don't understand. The select > talbe has an ORDER BY clause on it. This a technique commonly used to get paged data, it works in many other stored> procedures that I have written. This time however the rows are not being inserted into my temporary table in the proper> order (as specified by the ORDER BY clause that is plainly there that apparently you cannot see).Show quote > so that I can select a "page" of it to send to the> > > > Alejandro Mesa wrote: > > Buddy Ackerman, > > > > SQL Server does not asure any order of the rows returned by a "select" > > statement if the clause "order by" is not used. > > > > In case you are trying to number the rows returned by the select statement, > > here are a couple of links that can guide you. > > > > How to dynamically number rows in a SELECT Statement > > http://support.microsoft.com/default.aspx?scid=kb;en-us;186133 > > > > How do I return row numbers with my query? > > http://www.aspfaq.com/show.asp?id=2427 > > > > > > AMB > > > > > > > > "Buddy Ackerman" wrote: > > > > > >>Because I select it using the following query. > >> > >> > >> SELECT * > >> FROM #tmp > >> WHERE rownum >= @pagesize*(@pagenum-1)+1 and rownum <= @pagesize*(@pagenum) > >> > >> > >> > >> > >> > >>Alejandro Mesa wrote: > >> > >>>How do you know it is not being inserted in the order specified by the "order > >>>by" clause? > >>> > >>> > >>>AMB > >>> > >>>"Buddy Ackerman" wrote: > >>> > >>> > >>> > >>>>I'm inserting into a temp table with the intent of ordering the data > >>>>client. However, the data is not inserted in the order that is specified in the query.> >>>> specifies. I do this in other procs without a> >>>>The data is not in the table in the order that the ORDER BY clause > >>>>problem and even if I just execute the select statement the result is ordered as specified. Why won't it insert into> >>>>the table properly ordered. [ItemID] [int] NOT NULL , [ItemGUID]> >>>> > >>>> > >>>>Here's the whole messy thing: > >>>> > >>>> > >>>>CREATE TABLE #tmp (rownum int not null identity(1,1) primary key, > >>>>[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 , [URL] [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) SEName, ShortName, AboutDisabled, DisplayOrder,> >>>> > >>>> > >>>> > >>>> INSERT #tmp (ItemID, ItemGUID, ParentTopicID, OwningUserID, Name, > >>>>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) isnull(a.AvgRating, 0) AvgRating, isnull(a.NumComments, 0)> >>>> SELECT i.*, isnull(a.NumRatings,0) NumRatings, > >>>>NumComments, isnull(r.rating, 0), isnull(al.alertOwnerUserID, -1) avg(ir.rating*1.0) AvgRating, sum(ir.HasComment) NumComments> >>>> FROM Item i (nolock) join > >>>> (select ir.itemid, count(ir.rating) NumRatings, > >>>> from ItemRatings ir with (nolock) al.alertOwnerUserID = 20069715 and alertType = 1> >>>> where ir.TopicID = 90 > >>>> and ir.userAge between 0 and 255 > >>>> and ir.userGender = coalesce(null, ir.userGender) > >>>> group by ir.itemid) a on i.itemid = a.itemid > >>>> left join ItemRatings r on i.itemid = r.itemid and r.userid = 20069715 > >>>> left join Alerts al on i.itemid = al.itemid and Show quote > >>>> WHERE i.IsDisabled = 0 > >>>> and isnull(i.ZipCode, '') = coalesce(null, isnull(i.ZipCode, '')) > >>>> ORDER BY a.AvgRating desc, a.NumRatings desc, i.Name asc > >>>> > >> The select statement inserting into the temp table does have an ORDER BY clause and that is that one that is not
working. When run separately it returns the rows in the order specified when I add in the INSERT statement the rows are not insert in the order that is specified in the select statment. The only reason that I posted here is to see if someone recognized a known bug not to have some amatuers tell me about ORDER BY clauses (which it clearly has). Brian Selzer wrote: Show quote > The select statement you issue to retrieve the rows doesn't have an order by > clause, however. When you issue the select statement, are the rownumbers > returned in order? Can you post DDL and sample data? Can you post the > execution plan for the insert statement? It is extremely hard to provide > assistance without DDL and sample data. Although, after blasting Alejandro > Mesa like you did, I don't think many on this forum would be so inclined. > Of course, you can always pay Microsoft for help. I think the going rate is > $300 per incident. > > "Buddy Ackerman" <buddy_nospam@buddyackerman.com> wrote in message > news:ual4UMMtFHA.256@tk2msftngp13.phx.gbl... > >>Quite trying to answer a question you clearly don't understand. The select > > statement used to insert into the temporary > >>talbe has an ORDER BY clause on it. This a technique commonly used to get > > paged data, it works in many other stored > >>procedures that I have written. This time however the rows are not being > > inserted into my temporary table in the proper > >>order (as specified by the ORDER BY clause that is plainly there that > > apparently you cannot see). > >> >> >> >>Alejandro Mesa wrote: >> >>>Buddy Ackerman, >>> >>>SQL Server does not asure any order of the rows returned by a "select" >>>statement if the clause "order by" is not used. >>> >>>In case you are trying to number the rows returned by the select > > statement, > >>>here are a couple of links that can guide you. >>> >>>How to dynamically number rows in a SELECT Statement >>>http://support.microsoft.com/default.aspx?scid=kb;en-us;186133 >>> >>>How do I return row numbers with my query? >>>http://www.aspfaq.com/show.asp?id=2427 >>> >>> >>>AMB >>> >>> >>> >>>"Buddy Ackerman" wrote: >>> >>> >>> >>>>Because I select it using the following query. >>>> >>>> >>>> SELECT * >>>> FROM #tmp >>>> WHERE rownum >= @pagesize*(@pagenum-1)+1 and rownum <= > > @pagesize*(@pagenum) > >>>> >>>> >>>> >>>> >>>>Alejandro Mesa wrote: >>>> >>>> >>>>>How do you know it is not being inserted in the order specified by the > > "order > >>>>>by" clause? >>>>> >>>>> >>>>>AMB >>>>> >>>>>"Buddy Ackerman" wrote: >>>>> >>>>> >>>>> >>>>> >>>>>>I'm inserting into a temp table with the intent of ordering the data > > so that I can select a "page" of it to send to the > >>>>>>client. However, the data is not inserted in the order that is > > specified in the query. > >>>>>>The data is not in the table in the order that the ORDER BY clause > > specifies. I do this in other procs without a > >>>>>>problem and even if I just execute the select statement the result is > > ordered as specified. Why won't it insert into > >>>>>>the table properly ordered. >>>>>> >>>>>> >>>>>>Here's the whole messy thing: >>>>>> >>>>>> >>>>>>CREATE TABLE #tmp (rownum int not null identity(1,1) 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 , [URL] > > [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 = 90 >>>>>> and ir.userAge between 0 and 255 >>>>>> and ir.userGender = coalesce(null, ir.userGender) >>>>>> group by ir.itemid) a on i.itemid = a.itemid >>>>>> left join ItemRatings r on i.itemid = r.itemid and r.userid = > > 20069715 > >>>>>> left join Alerts al on i.itemid = al.itemid and > > al.alertOwnerUserID = 20069715 and alertType = 1 > >>>>>> WHERE i.IsDisabled = 0 >>>>>> and isnull(i.ZipCode, '') = coalesce(null, isnull(i.ZipCode, > > '')) > >>>>>> ORDER BY a.AvgRating desc, a.NumRatings desc, i.Name asc >>>>>> >>>> > > Well, I'll say one thing for you Buddy - you sure have moxy!
In this case, you are wrong and Alejandro, Brian, and Jens are correct. The order of insertion makes no difference. It does not matter whether it has worked for you one time, a hundred times, or a thousand times. By definition, a table is an unordered set of rows. This is true whether you are talking about a permanent table, temp table or table variable. The only way to guarantee a particular order is to apply the ORDER BY clause when you *retrieve* the data. This also means that there is no guarantee that Identity values will be applied to the data based on the order the data is inserted. In response to your specific circumstance, I think this will at least verify what everyone else is telling you: "The behavior of the IDENTITY function when used with SELECT INTO or INSERT ... SELECT queries that contain an ORDER BY clause" http://support.microsoft.com/default.aspx?scid=kb;en-us;273586 Show quote "Buddy Ackerman" <buddy_nospam@buddyackerman.com> wrote in message news:%23Fc97yNtFHA.3628@TK2MSFTNGP14.phx.gbl... > The select statement inserting into the temp table does have an ORDER BY > clause and that is that one that is not working. When run separately it > returns the rows in the order specified when I add in the INSERT statement > the rows are not insert in the order that is specified in the select > statment. > > The only reason that I posted here is to see if someone recognized a known > bug not to have some amatuers tell me about ORDER BY clauses (which it > clearly has). > > > > > > > Brian Selzer wrote: >> The select statement you issue to retrieve the rows doesn't have an order >> by >> clause, however. When you issue the select statement, are the rownumbers >> returned in order? Can you post DDL and sample data? Can you post the >> execution plan for the insert statement? It is extremely hard to provide >> assistance without DDL and sample data. Although, after blasting >> Alejandro >> Mesa like you did, I don't think many on this forum would be so inclined. >> Of course, you can always pay Microsoft for help. I think the going rate >> is >> $300 per incident. >> >> "Buddy Ackerman" <buddy_nospam@buddyackerman.com> wrote in message >> news:ual4UMMtFHA.256@tk2msftngp13.phx.gbl... >> >>>Quite trying to answer a question you clearly don't understand. The >>>select >> >> statement used to insert into the temporary >> >>>talbe has an ORDER BY clause on it. This a technique commonly used to >>>get >> >> paged data, it works in many other stored >> >>>procedures that I have written. This time however the rows are not being >> >> inserted into my temporary table in the proper >> >>>order (as specified by the ORDER BY clause that is plainly there that >> >> apparently you cannot see). >> >>> >>> >>> >>>Alejandro Mesa wrote: >>> >>>>Buddy Ackerman, >>>> >>>>SQL Server does not asure any order of the rows returned by a "select" >>>>statement if the clause "order by" is not used. >>>> >>>>In case you are trying to number the rows returned by the select >> >> statement, >> >>>>here are a couple of links that can guide you. >>>> >>>>How to dynamically number rows in a SELECT Statement >>>>http://support.microsoft.com/default.aspx?scid=kb;en-us;186133 >>>> >>>>How do I return row numbers with my query? >>>>http://www.aspfaq.com/show.asp?id=2427 >>>> >>>> >>>>AMB >>>> >>>> >>>> >>>>"Buddy Ackerman" wrote: >>>> >>>> >>>> >>>>>Because I select it using the following query. >>>>> >>>>> >>>>> SELECT * >>>>> FROM #tmp >>>>> WHERE rownum >= @pagesize*(@pagenum-1)+1 and rownum <= >> >> @pagesize*(@pagenum) >> >>>>> >>>>> >>>>> >>>>> >>>>>Alejandro Mesa wrote: >>>>> >>>>> >>>>>>How do you know it is not being inserted in the order specified by the >> >> "order >> >>>>>>by" clause? >>>>>> >>>>>> >>>>>>AMB >>>>>> >>>>>>"Buddy Ackerman" wrote: >>>>>> >>>>>> >>>>>> >>>>>> >>>>>>>I'm inserting into a temp table with the intent of ordering the data >> >> so that I can select a "page" of it to send to the >> >>>>>>>client. However, the data is not inserted in the order that is >> >> specified in the query. >> >>>>>>>The data is not in the table in the order that the ORDER BY clause >> >> specifies. I do this in other procs without a >> >>>>>>>problem and even if I just execute the select statement the result is >> >> ordered as specified. Why won't it insert into >> >>>>>>>the table properly ordered. >>>>>>> >>>>>>> >>>>>>>Here's the whole messy thing: >>>>>>> >>>>>>> >>>>>>>CREATE TABLE #tmp (rownum int not null identity(1,1) 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 , [URL] >> >> [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 = 90 >>>>>>> and ir.userAge between 0 and 255 >>>>>>> and ir.userGender = coalesce(null, ir.userGender) >>>>>>> group by ir.itemid) a on i.itemid = a.itemid >>>>>>> left join ItemRatings r on i.itemid = r.itemid and r.userid = >> >> 20069715 >> >>>>>>> left join Alerts al on i.itemid = al.itemid and >> >> al.alertOwnerUserID = 20069715 and alertType = 1 >> >>>>>>> WHERE i.IsDisabled = 0 >>>>>>> and isnull(i.ZipCode, '') = coalesce(null, isnull(i.ZipCode, >> >> '')) >> >>>>>>> ORDER BY a.AvgRating desc, a.NumRatings desc, i.Name asc >>>>>>> >>>>> >> OK, I will need to correct one sentence in my first post. After re-reading
the KB article, I see that the way you are adding rows to the temp table does guarantee that the IDENTITY values are assigned in the correct order. However, the last sentence of the article says: "Note ID identity column is generated to have same order as of Col1. However, INSERT INTO doesn't guarantee the physical order of either ID or Col1 in NewTable. To retrieve the data in desired order, an ORDER BY clause is required as shown by following SELECT statement: SELECT * from NewTable ORDER BY ID" So you still need to use an ORDER BY when selecting the 'page' of data. Show quote "Jeremy Williams" <jeremydw***@netscape.net> wrote in message news:uP%23gFJOtFHA.4080@TK2MSFTNGP12.phx.gbl... > Well, I'll say one thing for you Buddy - you sure have moxy! > > In this case, you are wrong and Alejandro, Brian, and Jens are correct. > The order of insertion makes no difference. It does not matter whether it > has worked for you one time, a hundred times, or a thousand times. By > definition, a table is an unordered set of rows. This is true whether you > are talking about a permanent table, temp table or table variable. The > only way to guarantee a particular order is to apply the ORDER BY clause > when you *retrieve* the data. This also means that there is no guarantee > that Identity values will be applied to the data based on the order the > data is inserted. > > In response to your specific circumstance, I think this will at least > verify what everyone else is telling you: > > "The behavior of the IDENTITY function when used with SELECT INTO or > INSERT .. SELECT queries that contain an ORDER BY clause" > http://support.microsoft.com/default.aspx?scid=kb;en-us;273586 > > > > "Buddy Ackerman" <buddy_nospam@buddyackerman.com> wrote in message > news:%23Fc97yNtFHA.3628@TK2MSFTNGP14.phx.gbl... >> The select statement inserting into the temp table does have an ORDER BY >> clause and that is that one that is not working. When run separately it >> returns the rows in the order specified when I add in the INSERT >> statement the rows are not insert in the order that is specified in the >> select statment. >> >> The only reason that I posted here is to see if someone recognized a >> known bug not to have some amatuers tell me about ORDER BY clauses (which >> it clearly has). >> >> >> >> >> >> >> Brian Selzer wrote: >>> The select statement you issue to retrieve the rows doesn't have an >>> order by >>> clause, however. When you issue the select statement, are the >>> rownumbers >>> returned in order? Can you post DDL and sample data? Can you post the >>> execution plan for the insert statement? It is extremely hard to >>> provide >>> assistance without DDL and sample data. Although, after blasting >>> Alejandro >>> Mesa like you did, I don't think many on this forum would be so >>> inclined. >>> Of course, you can always pay Microsoft for help. I think the going >>> rate is >>> $300 per incident. >>> >>> "Buddy Ackerman" <buddy_nospam@buddyackerman.com> wrote in message >>> news:ual4UMMtFHA.256@tk2msftngp13.phx.gbl... >>> >>>>Quite trying to answer a question you clearly don't understand. The >>>>select >>> >>> statement used to insert into the temporary >>> >>>>talbe has an ORDER BY clause on it. This a technique commonly used to >>>>get >>> >>> paged data, it works in many other stored >>> >>>>procedures that I have written. This time however the rows are not >>>>being >>> >>> inserted into my temporary table in the proper >>> >>>>order (as specified by the ORDER BY clause that is plainly there that >>> >>> apparently you cannot see). >>> >>>> >>>> >>>> >>>>Alejandro Mesa wrote: >>>> >>>>>Buddy Ackerman, >>>>> >>>>>SQL Server does not asure any order of the rows returned by a "select" >>>>>statement if the clause "order by" is not used. >>>>> >>>>>In case you are trying to number the rows returned by the select >>> >>> statement, >>> >>>>>here are a couple of links that can guide you. >>>>> >>>>>How to dynamically number rows in a SELECT Statement >>>>>http://support.microsoft.com/default.aspx?scid=kb;en-us;186133 >>>>> >>>>>How do I return row numbers with my query? >>>>>http://www.aspfaq.com/show.asp?id=2427 >>>>> >>>>> >>>>>AMB >>>>> >>>>> >>>>> >>>>>"Buddy Ackerman" wrote: >>>>> >>>>> >>>>> >>>>>>Because I select it using the following query. >>>>>> >>>>>> >>>>>> SELECT * >>>>>> FROM #tmp >>>>>> WHERE rownum >= @pagesize*(@pagenum-1)+1 and rownum <= >>> >>> @pagesize*(@pagenum) >>> >>>>>> >>>>>> >>>>>> >>>>>> >>>>>>Alejandro Mesa wrote: >>>>>> >>>>>> >>>>>>>How do you know it is not being inserted in the order specified by >>>>>>>the >>> >>> "order >>> >>>>>>>by" clause? >>>>>>> >>>>>>> >>>>>>>AMB >>>>>>> >>>>>>>"Buddy Ackerman" wrote: >>>>>>> >>>>>>> >>>>>>> >>>>>>> >>>>>>>>I'm inserting into a temp table with the intent of ordering the data >>> >>> so that I can select a "page" of it to send to the >>> >>>>>>>>client. However, the data is not inserted in the order that is >>> >>> specified in the query. >>> >>>>>>>>The data is not in the table in the order that the ORDER BY clause >>> >>> specifies. I do this in other procs without a >>> >>>>>>>>problem and even if I just execute the select statement the result >>>>>>>>is >>> >>> ordered as specified. Why won't it insert into >>> >>>>>>>>the table properly ordered. >>>>>>>> >>>>>>>> >>>>>>>>Here's the whole messy thing: >>>>>>>> >>>>>>>> >>>>>>>>CREATE TABLE #tmp (rownum int not null identity(1,1) 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 , [URL] >>> >>> [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 = 90 >>>>>>>> and ir.userAge between 0 and 255 >>>>>>>> and ir.userGender = coalesce(null, ir.userGender) >>>>>>>> group by ir.itemid) a on i.itemid = a.itemid >>>>>>>> left join ItemRatings r on i.itemid = r.itemid and r.userid = >>> >>> 20069715 >>> >>>>>>>> left join Alerts al on i.itemid = al.itemid and >>> >>> al.alertOwnerUserID = 20069715 and alertType = 1 >>> >>>>>>>> WHERE i.IsDisabled = 0 >>>>>>>> and isnull(i.ZipCode, '') = coalesce(null, isnull(i.ZipCode, >>> >>> '')) >>> >>>>>>>> ORDER BY a.AvgRating desc, a.NumRatings desc, i.Name asc >>>>>>>> >>>>>> >>> > Duh.
Jeremy Williams wrote: Show quote > OK, I will need to correct one sentence in my first post. After re-reading > the KB article, I see that the way you are adding rows to the temp table > does guarantee that the IDENTITY values are assigned in the correct order. > However, the last sentence of the article says: > > "Note ID identity column is generated to have same order as of Col1. > However, INSERT INTO doesn't guarantee the physical order of either ID or > Col1 in NewTable. To retrieve the data in desired order, an ORDER BY clause > is required as shown by following SELECT statement: SELECT * from NewTable > ORDER BY ID" > > So you still need to use an ORDER BY when selecting the 'page' of data. > > "Jeremy Williams" <jeremydw***@netscape.net> wrote in message > news:uP%23gFJOtFHA.4080@TK2MSFTNGP12.phx.gbl... > >>Well, I'll say one thing for you Buddy - you sure have moxy! >> >>In this case, you are wrong and Alejandro, Brian, and Jens are correct. >>The order of insertion makes no difference. It does not matter whether it >>has worked for you one time, a hundred times, or a thousand times. By >>definition, a table is an unordered set of rows. This is true whether you >>are talking about a permanent table, temp table or table variable. The >>only way to guarantee a particular order is to apply the ORDER BY clause >>when you *retrieve* the data. This also means that there is no guarantee >>that Identity values will be applied to the data based on the order the >>data is inserted. >> >>In response to your specific circumstance, I think this will at least >>verify what everyone else is telling you: >> >>"The behavior of the IDENTITY function when used with SELECT INTO or >>INSERT .. SELECT queries that contain an ORDER BY clause" >>http://support.microsoft.com/default.aspx?scid=kb;en-us;273586 >> >> >> >>"Buddy Ackerman" <buddy_nospam@buddyackerman.com> wrote in message >>news:%23Fc97yNtFHA.3628@TK2MSFTNGP14.phx.gbl... >> >>>The select statement inserting into the temp table does have an ORDER BY >>>clause and that is that one that is not working. When run separately it >>>returns the rows in the order specified when I add in the INSERT >>>statement the rows are not insert in the order that is specified in the >>>select statment. >>> >>>The only reason that I posted here is to see if someone recognized a >>>known bug not to have some amatuers tell me about ORDER BY clauses (which >>>it clearly has). >>> >>> >>> >>> >>> >>> >>>Brian Selzer wrote: >>> >>>>The select statement you issue to retrieve the rows doesn't have an >>>>order by >>>>clause, however. When you issue the select statement, are the >>>>rownumbers >>>>returned in order? Can you post DDL and sample data? Can you post the >>>>execution plan for the insert statement? It is extremely hard to >>>>provide >>>>assistance without DDL and sample data. Although, after blasting >>>>Alejandro >>>>Mesa like you did, I don't think many on this forum would be so >>>>inclined. >>>>Of course, you can always pay Microsoft for help. I think the going >>>>rate is >>>>$300 per incident. >>>> >>>>"Buddy Ackerman" <buddy_nospam@buddyackerman.com> wrote in message >>>>news:ual4UMMtFHA.256@tk2msftngp13.phx.gbl... >>>> >>>> >>>>>Quite trying to answer a question you clearly don't understand. The >>>>>select >>>> >>>>statement used to insert into the temporary >>>> >>>> >>>>>talbe has an ORDER BY clause on it. This a technique commonly used to >>>>>get >>>> >>>>paged data, it works in many other stored >>>> >>>> >>>>>procedures that I have written. This time however the rows are not >>>>>being >>>> >>>>inserted into my temporary table in the proper >>>> >>>> >>>>>order (as specified by the ORDER BY clause that is plainly there that >>>> >>>>apparently you cannot see). >>>> >>>> >>>>> >>>>> >>>>>Alejandro Mesa wrote: >>>>> >>>>> >>>>>>Buddy Ackerman, >>>>>> >>>>>>SQL Server does not asure any order of the rows returned by a "select" >>>>>>statement if the clause "order by" is not used. >>>>>> >>>>>>In case you are trying to number the rows returned by the select >>>> >>>>statement, >>>> >>>> >>>>>>here are a couple of links that can guide you. >>>>>> >>>>>>How to dynamically number rows in a SELECT Statement >>>>>>http://support.microsoft.com/default.aspx?scid=kb;en-us;186133 >>>>>> >>>>>>How do I return row numbers with my query? >>>>>>http://www.aspfaq.com/show.asp?id=2427 >>>>>> >>>>>> >>>>>>AMB >>>>>> >>>>>> >>>>>> >>>>>>"Buddy Ackerman" wrote: >>>>>> >>>>>> >>>>>> >>>>>> >>>>>>>Because I select it using the following query. >>>>>>> >>>>>>> >>>>>>> SELECT * >>>>>>> FROM #tmp >>>>>>> WHERE rownum >= @pagesize*(@pagenum-1)+1 and rownum <= >>>> >>>>@pagesize*(@pagenum) >>>> >>>> >>>>>>> >>>>>>> >>>>>>> >>>>>>>Alejandro Mesa wrote: >>>>>>> >>>>>>> >>>>>>> >>>>>>>>How do you know it is not being inserted in the order specified by >>>>>>>>the >>>> >>>>"order >>>> >>>> >>>>>>>>by" clause? >>>>>>>> >>>>>>>> >>>>>>>>AMB >>>>>>>> >>>>>>>>"Buddy Ackerman" wrote: >>>>>>>> >>>>>>>> >>>>>>>> >>>>>>>> >>>>>>>> >>>>>>>>>I'm inserting into a temp table with the intent of ordering the data >>>> >>>>so that I can select a "page" of it to send to the >>>> >>>> >>>>>>>>>client. However, the data is not inserted in the order that is >>>> >>>>specified in the query. >>>> >>>> >>>>>>>>>The data is not in the table in the order that the ORDER BY clause >>>> >>>>specifies. I do this in other procs without a >>>> >>>> >>>>>>>>>problem and even if I just execute the select statement the result >>>>>>>>>is >>>> >>>>ordered as specified. Why won't it insert into >>>> >>>> >>>>>>>>>the table properly ordered. >>>>>>>>> >>>>>>>>> >>>>>>>>>Here's the whole messy thing: >>>>>>>>> >>>>>>>>> >>>>>>>>>CREATE TABLE #tmp (rownum int not null identity(1,1) 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 , [URL] >>>> >>>>[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 = 90 >>>>>>>>> and ir.userAge between 0 and 255 >>>>>>>>> and ir.userGender = coalesce(null, ir.userGender) >>>>>>>>> group by ir.itemid) a on i.itemid = a.itemid >>>>>>>>> left join ItemRatings r on i.itemid = r.itemid and r.userid = >>>> >>>>20069715 >>>> >>>> >>>>>>>>> left join Alerts al on i.itemid = al.itemid and >>>> >>>>al.alertOwnerUserID = 20069715 and alertType = 1 >>>> >>>> >>>>>>>>> WHERE i.IsDisabled = 0 >>>>>>>>> and isnull(i.ZipCode, '') = coalesce(null, isnull(i.ZipCode, >>>> >>>>'')) >>>> >>>> >>>>>>>>> ORDER BY a.AvgRating desc, a.NumRatings desc, i.Name asc >>>>>>>>> >>>>>>> > > Yes - Duh! Since you did not show an ORDR BY clause in your retrieval
statement, we were all thinking Duh as well. Thanks for agreeing! Show quote "Buddy Ackerman" <buddy_nospam@buddyackerman.com> wrote in message isnull(i.ZipCode,news:#ZMle8OtFHA.3604@tk2msftngp13.phx.gbl... > Duh. > > > > Jeremy Williams wrote: > > OK, I will need to correct one sentence in my first post. After re-reading > > the KB article, I see that the way you are adding rows to the temp table > > does guarantee that the IDENTITY values are assigned in the correct order. > > However, the last sentence of the article says: > > > > "Note ID identity column is generated to have same order as of Col1. > > However, INSERT INTO doesn't guarantee the physical order of either ID or > > Col1 in NewTable. To retrieve the data in desired order, an ORDER BY clause > > is required as shown by following SELECT statement: SELECT * from NewTable > > ORDER BY ID" > > > > So you still need to use an ORDER BY when selecting the 'page' of data. > > > > "Jeremy Williams" <jeremydw***@netscape.net> wrote in message > > news:uP%23gFJOtFHA.4080@TK2MSFTNGP12.phx.gbl... > > > >>Well, I'll say one thing for you Buddy - you sure have moxy! > >> > >>In this case, you are wrong and Alejandro, Brian, and Jens are correct. > >>The order of insertion makes no difference. It does not matter whether it > >>has worked for you one time, a hundred times, or a thousand times. By > >>definition, a table is an unordered set of rows. This is true whether you > >>are talking about a permanent table, temp table or table variable. The > >>only way to guarantee a particular order is to apply the ORDER BY clause > >>when you *retrieve* the data. This also means that there is no guarantee > >>that Identity values will be applied to the data based on the order the > >>data is inserted. > >> > >>In response to your specific circumstance, I think this will at least > >>verify what everyone else is telling you: > >> > >>"The behavior of the IDENTITY function when used with SELECT INTO or > >>INSERT .. SELECT queries that contain an ORDER BY clause" > >>http://support.microsoft.com/default.aspx?scid=kb;en-us;273586 > >> > >> > >> > >>"Buddy Ackerman" <buddy_nospam@buddyackerman.com> wrote in message > >>news:%23Fc97yNtFHA.3628@TK2MSFTNGP14.phx.gbl... > >> > >>>The select statement inserting into the temp table does have an ORDER BY > >>>clause and that is that one that is not working. When run separately it > >>>returns the rows in the order specified when I add in the INSERT > >>>statement the rows are not insert in the order that is specified in the > >>>select statment. > >>> > >>>The only reason that I posted here is to see if someone recognized a > >>>known bug not to have some amatuers tell me about ORDER BY clauses (which > >>>it clearly has). > >>> > >>> > >>> > >>> > >>> > >>> > >>>Brian Selzer wrote: > >>> > >>>>The select statement you issue to retrieve the rows doesn't have an > >>>>order by > >>>>clause, however. When you issue the select statement, are the > >>>>rownumbers > >>>>returned in order? Can you post DDL and sample data? Can you post the > >>>>execution plan for the insert statement? It is extremely hard to > >>>>provide > >>>>assistance without DDL and sample data. Although, after blasting > >>>>Alejandro > >>>>Mesa like you did, I don't think many on this forum would be so > >>>>inclined. > >>>>Of course, you can always pay Microsoft for help. I think the going > >>>>rate is > >>>>$300 per incident. > >>>> > >>>>"Buddy Ackerman" <buddy_nospam@buddyackerman.com> wrote in message > >>>>news:ual4UMMtFHA.256@tk2msftngp13.phx.gbl... > >>>> > >>>> > >>>>>Quite trying to answer a question you clearly don't understand. The > >>>>>select > >>>> > >>>>statement used to insert into the temporary > >>>> > >>>> > >>>>>talbe has an ORDER BY clause on it. This a technique commonly used to > >>>>>get > >>>> > >>>>paged data, it works in many other stored > >>>> > >>>> > >>>>>procedures that I have written. This time however the rows are not > >>>>>being > >>>> > >>>>inserted into my temporary table in the proper > >>>> > >>>> > >>>>>order (as specified by the ORDER BY clause that is plainly there that > >>>> > >>>>apparently you cannot see). > >>>> > >>>> > >>>>> > >>>>> > >>>>>Alejandro Mesa wrote: > >>>>> > >>>>> > >>>>>>Buddy Ackerman, > >>>>>> > >>>>>>SQL Server does not asure any order of the rows returned by a "select" > >>>>>>statement if the clause "order by" is not used. > >>>>>> > >>>>>>In case you are trying to number the rows returned by the select > >>>> > >>>>statement, > >>>> > >>>> > >>>>>>here are a couple of links that can guide you. > >>>>>> > >>>>>>How to dynamically number rows in a SELECT Statement > >>>>>>http://support.microsoft.com/default.aspx?scid=kb;en-us;186133 > >>>>>> > >>>>>>How do I return row numbers with my query? > >>>>>>http://www.aspfaq.com/show.asp?id=2427 > >>>>>> > >>>>>> > >>>>>>AMB > >>>>>> > >>>>>> > >>>>>> > >>>>>>"Buddy Ackerman" wrote: > >>>>>> > >>>>>> > >>>>>> > >>>>>> > >>>>>>>Because I select it using the following query. > >>>>>>> > >>>>>>> > >>>>>>> SELECT * > >>>>>>> FROM #tmp > >>>>>>> WHERE rownum >= @pagesize*(@pagenum-1)+1 and rownum <= > >>>> > >>>>@pagesize*(@pagenum) > >>>> > >>>> > >>>>>>> > >>>>>>> > >>>>>>> > >>>>>>>Alejandro Mesa wrote: > >>>>>>> > >>>>>>> > >>>>>>> > >>>>>>>>How do you know it is not being inserted in the order specified by > >>>>>>>>the > >>>> > >>>>"order > >>>> > >>>> > >>>>>>>>by" clause? > >>>>>>>> > >>>>>>>> > >>>>>>>>AMB > >>>>>>>> > >>>>>>>>"Buddy Ackerman" wrote: > >>>>>>>> > >>>>>>>> > >>>>>>>> > >>>>>>>> > >>>>>>>> > >>>>>>>>>I'm inserting into a temp table with the intent of ordering the data > >>>> > >>>>so that I can select a "page" of it to send to the > >>>> > >>>> > >>>>>>>>>client. However, the data is not inserted in the order that is > >>>> > >>>>specified in the query. > >>>> > >>>> > >>>>>>>>>The data is not in the table in the order that the ORDER BY clause > >>>> > >>>>specifies. I do this in other procs without a > >>>> > >>>> > >>>>>>>>>problem and even if I just execute the select statement the result > >>>>>>>>>is > >>>> > >>>>ordered as specified. Why won't it insert into > >>>> > >>>> > >>>>>>>>>the table properly ordered. > >>>>>>>>> > >>>>>>>>> > >>>>>>>>>Here's the whole messy thing: > >>>>>>>>> > >>>>>>>>> > >>>>>>>>>CREATE TABLE #tmp (rownum int not null identity(1,1) 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 , [URL] > >>>> > >>>>[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 = 90 > >>>>>>>>> and ir.userAge between 0 and 255 > >>>>>>>>> and ir.userGender = coalesce(null, ir.userGender) > >>>>>>>>> group by ir.itemid) a on i.itemid = a.itemid > >>>>>>>>> left join ItemRatings r on i.itemid = r.itemid and r.userid = > >>>> > >>>>20069715 > >>>> > >>>> > >>>>>>>>> left join Alerts al on i.itemid = al.itemid and > >>>> > >>>>al.alertOwnerUserID = 20069715 and alertType = 1 > >>>> > >>>> > >>>>>>>>> WHERE i.IsDisabled = 0 > >>>>>>>>> and isnull(i.ZipCode, '') = coalesce(null, Show quote > >>>> > >>>>'')) > >>>> > >>>> > >>>>>>>>> ORDER BY a.AvgRating desc, a.NumRatings desc, i.Name asc > >>>>>>>>> > >>>>>>> > > > > The retrieval statement was never the issue. No one ever looked at the original post properly (and still haven't apparently). Jeremy Williams wrote: Show quote > Yes - Duh! Since you did not show an ORDR BY clause in your retrieval > statement, we were all thinking Duh as well. Thanks for agreeing! > > "Buddy Ackerman" <buddy_nospam@buddyackerman.com> wrote in message > news:#ZMle8OtFHA.3604@tk2msftngp13.phx.gbl... > >>Duh. >> >> >> >>Jeremy Williams wrote: >> >>>OK, I will need to correct one sentence in my first post. After > > re-reading > >>>the KB article, I see that the way you are adding rows to the temp table >>>does guarantee that the IDENTITY values are assigned in the correct > > order. > >>>However, the last sentence of the article says: >>> >>>"Note ID identity column is generated to have same order as of Col1. >>>However, INSERT INTO doesn't guarantee the physical order of either ID > > or > >>>Col1 in NewTable. To retrieve the data in desired order, an ORDER BY > > clause > >>>is required as shown by following SELECT statement: SELECT * from > > NewTable > >>>ORDER BY ID" >>> >>>So you still need to use an ORDER BY when selecting the 'page' of data. >>> >>>"Jeremy Williams" <jeremydw***@netscape.net> wrote in message >>>news:uP%23gFJOtFHA.4080@TK2MSFTNGP12.phx.gbl... >>> >>> >>>>Well, I'll say one thing for you Buddy - you sure have moxy! >>>> >>>>In this case, you are wrong and Alejandro, Brian, and Jens are correct. >>>>The order of insertion makes no difference. It does not matter whether > > it > >>>>has worked for you one time, a hundred times, or a thousand times. By >>>>definition, a table is an unordered set of rows. This is true whether > > you > >>>>are talking about a permanent table, temp table or table variable. The >>>>only way to guarantee a particular order is to apply the ORDER BY clause >>>>when you *retrieve* the data. This also means that there is no guarantee >>>>that Identity values will be applied to the data based on the order the >>>>data is inserted. >>>> >>>>In response to your specific circumstance, I think this will at least >>>>verify what everyone else is telling you: >>>> >>>>"The behavior of the IDENTITY function when used with SELECT INTO or >>>>INSERT .. SELECT queries that contain an ORDER BY clause" >>>>http://support.microsoft.com/default.aspx?scid=kb;en-us;273586 >>>> >>>> >>>> >>>>"Buddy Ackerman" <buddy_nospam@buddyackerman.com> wrote in message >>>>news:%23Fc97yNtFHA.3628@TK2MSFTNGP14.phx.gbl... >>>> >>>> >>>>>The select statement inserting into the temp table does have an ORDER > > BY > >>>>>clause and that is that one that is not working. When run separately > > it > >>>>>returns the rows in the order specified when I add in the INSERT >>>>>statement the rows are not insert in the order that is specified in the >>>>>select statment. >>>>> >>>>>The only reason that I posted here is to see if someone recognized a >>>>>known bug not to have some amatuers tell me about ORDER BY clauses > > (which > >>>>>it clearly has). >>>>> >>>>> >>>>> >>>>> >>>>> >>>>> >>>>>Brian Selzer wrote: >>>>> >>>>> >>>>>>The select statement you issue to retrieve the rows doesn't have an >>>>>>order by >>>>>>clause, however. When you issue the select statement, are the >>>>>>rownumbers >>>>>>returned in order? Can you post DDL and sample data? Can you post > > the > >>>>>>execution plan for the insert statement? It is extremely hard to >>>>>>provide >>>>>>assistance without DDL and sample data. Although, after blasting >>>>>>Alejandro >>>>>>Mesa like you did, I don't think many on this forum would be so >>>>>>inclined. >>>>>>Of course, you can always pay Microsoft for help. I think the going >>>>>>rate is >>>>>>$300 per incident. >>>>>> >>>>>>"Buddy Ackerman" <buddy_nospam@buddyackerman.com> wrote in message >>>>>>news:ual4UMMtFHA.256@tk2msftngp13.phx.gbl... >>>>>> >>>>>> >>>>>> >>>>>>>Quite trying to answer a question you clearly don't understand. The >>>>>>>select >>>>>> >>>>>>statement used to insert into the temporary >>>>>> >>>>>> >>>>>> >>>>>>>talbe has an ORDER BY clause on it. This a technique commonly used > > to > >>>>>>>get >>>>>> >>>>>>paged data, it works in many other stored >>>>>> >>>>>> >>>>>> >>>>>>>procedures that I have written. This time however the rows are not >>>>>>>being >>>>>> >>>>>>inserted into my temporary table in the proper >>>>>> >>>>>> >>>>>> >>>>>>>order (as specified by the ORDER BY clause that is plainly there that >>>>>> >>>>>>apparently you cannot see). >>>>>> >>>>>> >>>>>> >>>>>>> >>>>>>>Alejandro Mesa wrote: >>>>>>> >>>>>>> >>>>>>> >>>>>>>>Buddy Ackerman, >>>>>>>> >>>>>>>>SQL Server does not asure any order of the rows returned by a > > "select" > >>>>>>>>statement if the clause "order by" is not used. >>>>>>>> >>>>>>>>In case you are trying to number the rows returned by the select >>>>>> >>>>>>statement, >>>>>> >>>>>> >>>>>> >>>>>>>>here are a couple of links that can guide you. >>>>>>>> >>>>>>>>How to dynamically number rows in a SELECT Statement >>>>>>>>http://support.microsoft.com/default.aspx?scid=kb;en-us;186133 >>>>>>>> >>>>>>>>How do I return row numbers with my query? >>>>>>>>http://www.aspfaq.com/show.asp?id=2427 >>>>>>>> >>>>>>>> >>>>>>>>AMB >>>>>>>> >>>>>>>> >>>>>>>> >>>>>>>>"Buddy Ackerman" wrote: >>>>>>>> >>>>>>>> >>>>>>>> >>>>>>>> >>>>>>>> >>>>>>>>>Because I select it using the following query. >>>>>>>>> >>>>>>>>> >>>>>>>>> SELECT * >>>>>>>>> FROM #tmp >>>>>>>>> WHERE rownum >= @pagesize*(@pagenum-1)+1 and rownum <= >>>>>> >>>>>>@pagesize*(@pagenum) >>>>>> >>>>>> >>>>>> >>>>>>>>> >>>>>>>>> >>>>>>>>>Alejandro Mesa wrote: >>>>>>>>> >>>>>>>>> >>>>>>>>> >>>>>>>>> >>>>>>>>>>How do you know it is not being inserted in the order specified by >>>>>>>>>>the >>>>>> >>>>>>"order >>>>>> >>>>>> >>>>>> >>>>>>>>>>by" clause? >>>>>>>>>> >>>>>>>>>> >>>>>>>>>>AMB >>>>>>>>>> >>>>>>>>>>"Buddy Ackerman" wrote: >>>>>>>>>> >>>>>>>>>> >>>>>>>>>> >>>>>>>>>> >>>>>>>>>> >>>>>>>>>> >>>>>>>>>>>I'm inserting into a temp table with the intent of ordering the > > data > >>>>>>so that I can select a "page" of it to send to the >>>>>> >>>>>> >>>>>> >>>>>>>>>>>client. However, the data is not inserted in the order that is >>>>>> >>>>>>specified in the query. >>>>>> >>>>>> >>>>>> >>>>>>>>>>>The data is not in the table in the order that the ORDER BY > > clause > >>>>>>specifies. I do this in other procs without a >>>>>> >>>>>> >>>>>> >>>>>>>>>>>problem and even if I just execute the select statement the > > result > >>>>>>>>>>>is >>>>>> >>>>>>ordered as specified. Why won't it insert into >>>>>> >>>>>> >>>>>> >>>>>>>>>>>the table properly ordered. >>>>>>>>>>> >>>>>>>>>>> >>>>>>>>>>>Here's the whole messy thing: >>>>>>>>>>> >>>>>>>>>>> >>>>>>>>>>>CREATE TABLE #tmp (rownum int not null identity(1,1) 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 >>>>>> >>>>>> >>&g | |||||||||||||||||||||||
