Can't get ORDER BY to work - 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

General SQL Server Forums

New to SQL Server Programming

Can't get ORDER BY to work

Author

Topic

jwvandongen
Starting Member

1 Post

Posted - 2012-08-25 : 16:20:16

Dear all,

I just don't get the ORDER BY clausule to work for my query. Maybe you here can help me with this issue.

I have this small piece of code:

DECLARE @table1 TABLE(RowID int not null primary key identity(1,1), id varchar(255), sEmployee varchar(255), iWeekdays varchar(255), iWeekends varchar(255), sCountry varchar(255), iHolidays varchar(255), iSalary varchar(255))

INSERT into @table1 (id, sEmployee,sCountry) (
SELECT a.ID, b.fullname, a.FreeGuidField_01
FROM [SynergyTestServer].[dbo].[Absences] AS a
LEFT JOIN [SynergyTestServer].[dbo].[humres] AS b
ON a.EmpID = b.res_id
WHERE a.Type='9549' AND a.Status='1'
ORDER BY b.fullname)

But i keep getting the following error:

Msg 156, Level 15, State 1, Line 25
Incorrect syntax near the keyword 'ORDER'.

but when i run the query without trying to insert it into a temporary table, the query works fine.

What am I doing wrong?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-08-25 : 17:05:54

remove braces around select


INSERT into @table1 (id, sEmployee,sCountry) 
SELECT a.ID, b.fullname, a.FreeGuidField_01 
FROM [SynergyTestServer].[dbo].[Absences] AS a 
LEFT JOIN [SynergyTestServer].[dbo].[humres] AS b 
ON a.EmpID = b.res_id 
WHERE a.Type='9549' AND a.Status='1' 
ORDER BY b.fullname

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

bitsmed
Aged Yak Warrior

545 Posts

Posted - 2012-08-25 : 18:35:34

I don't see why you would want to sort data at the time of inserting. When you select from the temp table, you can't be absolutely sure, that your data will be returned in insert-order (as far as I know).
Why not do the "order by sEmployee" when selecting from the temp table?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2012-08-25 : 18:41:34

quote:
Originally posted by bitsmed

I don't see why you would want to sort data at the time of inserting. When you select from the temp table, you can't be absolutely sure, that your data will be returned in insert-order (as far as I know).
Why not do the "order by sEmployee" when selecting from the temp table?

The order by matters because of the identity column. The OP likely wants the id sorted by sEmployee for further processing of the data.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

bitsmed
Aged Yak Warrior

545 Posts

Posted - 2012-08-25 : 19:32:37

I stand corrected tkizer.

I cannot begin to guess what further operations the OP want to do on the table. Sorry OP.

Have in mind though, that "There are no physical ordering guarantees on any insert operations in SQL 2005" <-- quote from MSSQL developer, found here [url]http://blogs.msdn.com/b/sqltips/archive/2005/07/20/441053.aspx[/url]

But then again, SQL 2005 is probably not used here, and not relevant here.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources