Select query using order by clause with temp table

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

SQL Server 2005 Forums

Transact-SQL (2005)

Select query using order by clause with temp table

Author

Topic

cplusplus
Aged Yak Warrior

567 Posts

Posted - 2010-03-11 : 11:52:43

How to use order by clause with the below select query to insert rows to temp table.

SET @n = (select distinct a.element_id from Tab_element a Inner join TAB_UserAccess AS B ON (a.ProjID = B.ProjID and a.deleted='0' and b.userid=@userid) order by a.element_id)
insert into #TEMPPrevNextElement(element_id)

EXEC sp_executesql @n

error message:
The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions

Thank you very much for the helpful info.

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2010-03-11 : 11:56:41

Your query doesn't maek any sense.

Do you mean? SLEECT TOP 1 ... ?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-11 : 12:05:42

you're trying to assign result of query to variable. It will not work expected way unless query returns only a single value. also i didnt understand what you're doing by this
EXEC sp_executesql @n

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

namman
Constraint Violating Yak Guru

285 Posts

Posted - 2010-03-11 : 13:12:11

error message:
The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions.

Because your select statemant does not except the ORDER BY clause, unless you include the TOP in the select as Lamprey mentioned.

Also pay attention to visakh16 message. Even if you fix the bug mentioned previously, your query may get another error message because the select statement may return more than 1 value.

Yeah, your code is a bit confusing. Point out clearly what you want. Lot of great people over here can help you, I think.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-03-12 : 02:17:17

insert into #TEMPPrevNextElement(element_id)
select distinct a.element_id from Tab_element a Inner join TAB_UserAccess AS B ON (a.ProjID = B.ProjID and a.deleted='0' and b.userid=@userid) order by a.element_id

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources