order by in subqueries - 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 2000 Forums

Transact-SQL (2000)

order by in subqueries

Author

Topic

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2004-09-09 : 07:44:09

i want to have the result returned by the query to be sorted so that it inserts the rows accordingly into a table. it returned an error on "order". is there a workaround this?

@Object is table data type and has 3 fields (identity field, username and objname)

insert into @Object(username,objname)
(SELECT usr.name as UserName,obj.name AS ObjectName
FROM
sysusers AS usr
join syspermissions AS per
ON usr.uid=per.grantee
JOIN sysobjects AS obj
ON obj.id = per.id
WHERE
obj.xtype IN ('U', 'P', 'FN')
AND obj.name NOT LIKE 'dt_%'
AND usr.name in (select * from @users)
ORDER BY usr.name)

i'm using the identity column to traverse the table

thanks in advance...

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-09-09 : 08:01:39

your query is ok, you just don't need ( and ) around select
and change * to name or whatever column you need in (select * from @users)


insert into @Object(username,objname)
SELECT 	usr.name as UserName,obj.name AS ObjectName 
FROM 	sysusers AS usr
		join syspermissions AS per ON usr.uid=per.grantee
		JOIN sysobjects AS obj ON obj.id = per.id
WHERE	obj.xtype IN ('U', 'P', 'FN')
	AND obj.name NOT LIKE 'dt_%'
	AND usr.name in (select name from @users)
ORDER BY usr.name

Go with the flow & have fun! Else fight the flow

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2004-09-09 : 08:04:50

[code]insert into @Object(username,objname)
SELECT UserName, ObjectName FROM
(SELECT TOP 100 PERCENT usr.name as UserName,obj.name AS ObjectName
FROM
sysusers AS usr
join syspermissions AS per
ON usr.uid=per.grantee
JOIN sysobjects AS obj
ON obj.id = per.id
WHERE
obj.xtype IN ('U', 'P', 'FN')
AND obj.name NOT LIKE 'dt_%'
AND usr.name not in (select * from @users) -- plz! select name from @users
ORDER BY usr.name) t[/code]

Why on earth would you need to do this ?!?!?
If order matters, why don't you create a #temp table with clustered index on UserName + ObjectName ?

rockmoose
/* Chaos is the nature of things...Order is a lesser state of chaos */

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2004-09-09 : 08:07:46

Ooops
spirit1 is right,
but if you ever need to order a subquery, use TOP 100 PERCENT

Edit: Besides, the @users is out of scope in the subquery,
( I remember the previous post now )

rockmoose
/* Chaos is the nature of things...Order is a lesser state of chaos */

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2004-09-09 : 08:17:34

thanks guys, i already removed the *, sorry forgot to edit the one posted.

anyways, it was the parentheses!

lolz

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2004-09-09 : 08:19:59

the @users is another table

I'm passing some values to it and getting the permissions per user. the information_schema don't provide me with the data i need.

what a day! parentheses! lolz

quote:
Originally posted by rockmoose

Ooops
spirit1 is right,
but if you ever need to order a subquery, use TOP 100 PERCENT

Edit: Besides, the @users is out of scope in the subquery,
( I remember the previous post now )

rockmoose
/* Chaos is the nature of things...Order is a lesser state of chaos */

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-09-09 : 08:30:46

yeah we all have those kind of days...

mine was last week all week

Go with the flow & have fun! Else fight the flow

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources