Error - 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

SQL Server 2005 Forums

Transact-SQL (2005)

Error

Author

Topic

maximus007
Starting Member

18 Posts

Posted - 2009-05-14 : 14:46:56

Hi everyone;
I am somewhat new at sql so excuse my ignorance. I am getting the following error and not sure how to fix it.
Thanks.

Error:"The ORDER BY clause is invalid in views, inline functions, derived tables, and subqueries, unless TOP is also specified."

The sql statement:
select
mg.GroupId as '@id'
,mg.ParentId as '@parentid'
,mg.GroupDesc as '@groupdesc'
,mg.GroupText as '@grouptext'
--menu items
,(
select mi.MenuId as '@id'
,mi.ActionId as '@actionid'
,mi.GroupId as '@groupid'
,mi.MenuText as '@menutext'
,mi.MenuDesc as '@menudesc'
,mi.Url as '@url'
from MenuItems mi
where mi.Enabled = 1 and mi.GroupId = mg.GroupId order by mi.Sort
)
from MenuGroups mg
where mg.Enabled = 1
order by mg.ParentId, mg.Sort

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2009-05-14 : 14:52:36

SELECT TOP 100 Percent
...

You can read more on TOP in Books On Line

Jim

maximus007
Starting Member

18 Posts

Posted - 2009-05-14 : 15:21:09

Thank you Jim for your quick reply. ok. does it mean I have to creat a view in order to get this statement to work?
I am a little confuse on how to apply the SELECT TOP 100 Percent to my statment.

thanks

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2009-05-14 : 15:31:01

Sorry, I missed something in your op, I assumed this query was a view
select
mg.GroupId as '@id'
,mg.ParentId as '@parentid'
,mg.GroupDesc as '@groupdesc'
,mg.GroupText as '@grouptext'
--menu items
,(
select mi.MenuId as '@id'
,mi.ActionId as '@actionid'
,mi.GroupId as '@groupid'
,mi.MenuText as '@menutext'
,mi.MenuDesc as '@menudesc'
,mi.Url as '@url'
from MenuItems mi
where mi.Enabled = 1 and mi.GroupId = mg.GroupId
~~order by mi.Sort~~ can't do this
)
from MenuGroups mg
where mg.Enabled = 1
order by mg.ParentId, mg.Sort

Jim

maximus007
Starting Member

18 Posts

Posted - 2009-05-14 : 15:34:33

I had tried that way and I got Error:Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2009-05-14 : 15:38:10

Yes, the more I look at the query, the more confused I get! What are you trying to accomplish with this query. You can't do multiple columns in the sub select, SQL Server wants that to be jsut one column. I believe a simple inner join may be what you need.

Jim

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2009-05-14 : 15:41:56

SELECT
mg.GroupId
,mg.ParentId
,mg.GroupDesc
,mg.GroupText
,mi.MenuId
,mi.ActionId
,mi.GroupId
,mi.MenuText
,mi.MenuDesc
,mi.Url
FROM
menuItems mi
INNER JOIN
MenuGroups mg
ON
mi.GroupID = mg.GroupID
and mg.enabled = 1
and mi.enabled = 1

maybe? The @ in front of each column alias is a bad idea.

Jim

maximus007
Starting Member

18 Posts

Posted - 2009-05-14 : 15:42:45

I am trying to accomplish a data driven nav tree but i will try the inner join

Thanks

max

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2009-05-14 : 15:49:11

If you are still having trouble this link will help you to help us help you :) :
[url]http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx[/url]

Possibly adding an ORDER BY to Jimf's query will help get the results you want??

ORDER BY 
	mg.ParentId, 
	mg.Sort,
	mi.Sort

maximus007
Starting Member

18 Posts

Posted - 2009-05-14 : 15:53:42

Foolish of me to think that the subquery was the only way. By way it works

Thank you

max

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources