InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comSat, 18 May 2013 09:59:42 GMT20http://www.sqlservercentral.com/Forums/Topic983676-338-1.aspxThere is no guarantee that rows will be returned in any particular unless ORDER BY is specified.There is also no guarantee that rows will be inserted in a particular order, even if the inserts are sourced as SELECT...ORDER BY...[url=http://sqlblogcasts.com/blogs/sqlandthelike/archive/2010/06/27/sql-101-without-order-by-order-is-not-guaranteed.aspx]http://sqlblogcasts.com/blogs/sqlandthelike/archive/2010/06/27/sql-101-without-order-by-order-is-not-guaranteed.aspx[/url]Fri, 10 Sep 2010 06:16:17 GMTChrisM@Workhttp://www.sqlservercentral.com/Forums/Topic983676-338-1.aspxIf you want to retrieve rows in a particular order, you must put an ORDER BY on the statement that retrieves the data with the appropriate columns listedAn order by on the insert only affects identity columns (if present). It does not guarantee the order that data will be returned from that tableFri, 10 Sep 2010 06:15:36 GMTGilaMonsterhttp://www.sqlservercentral.com/Forums/Topic983676-338-1.aspxHi,I have test database with following script. I am trying to explain my problem with this sample db script. I am creating a temp. table with the ordered column from other table and then using that table to join the other table. If you notice the output of the below select query, the returned rows from first table are in the sequence of insertion not in the sequence of the temp. table.Is there any other way to retrieve rows in the sequence of temp. (joined) table?[code="sql"]CREATE TABLE [dbo].[Table_2]( [c1] [int] NULL, [c2] [nvarchar](50) NULL) ON [PRIMARY]GOINSERT [dbo].[Table_2] ([c1], [c2]) VALUES (1, N'z')INSERT [dbo].[Table_2] ([c1], [c2]) VALUES (2, N'y')INSERT [dbo].[Table_2] ([c1], [c2]) VALUES (3, N'x')INSERT [dbo].[Table_2] ([c1], [c2]) VALUES (4, N'a')INSERT [dbo].[Table_2] ([c1], [c2]) VALUES (5, N'b')INSERT [dbo].[Table_2] ([c1], [c2]) VALUES (6, N'c')CREATE TABLE [dbo].[Table_1]( [c1] [int] NULL, [c2] [nvarchar](50) NULL) ON [PRIMARY]GOINSERT [dbo].[Table_1] ([c1], [c2]) VALUES (3, N'x')INSERT [dbo].[Table_1] ([c1], [c2]) VALUES (2, N'y')INSERT [dbo].[Table_1] ([c1], [c2]) VALUES (1, N'z')INSERT [dbo].[Table_1] ([c1], [c2]) VALUES (6, N'c')INSERT [dbo].[Table_1] ([c1], [c2]) VALUES (5, N'b')INSERT [dbo].[Table_1] ([c1], [c2]) VALUES (4, N'a')----------------------------------------------------IF OBJECT_ID('TempDB..#tblTemp','U') IS NOT NULL DROP TABLE #tblTemp CREATE TABLE #tblTemp (Id int);INSERT INTO #tblTemp SELECT c1 FROM Table_2 ORDER BY c2SELECT * FROM Table_1 INNER JOIN #tblTemp ON Id=Table_1.c1 DROP TABLE #tblTemp[/code]Regards,JigsFri, 10 Sep 2010 05:53:18 GMTjdp12383