ORDER BY and UNION together again!

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

ORDER BY and UNION together again!

Author

Topic

saulfeliz
Starting Member

14 Posts

Posted - 2012-01-26 : 14:57:49

Hey Forumers

I’m trying to UNION two+ nested queries, and then sort it with an ORDER BY in the end. But I’m getting errors. Here’s the query:

SELECT DISTINCT Count (*) UID_XID AS 'Interactors' FROM [TABLE NAME] WHERE
VARIABLE_1 IN ('ASDQ6KJ8un4Q')
AND VARIABLE_2 IN (11)
AND UID_XID IN (
SELECT DISTINCT UID_XID FROM [TABLE NAME] WHERE
VARIABLE_1 IN ('ASDQ6KJ8un4Q')
AND VARIABLE_2 IN (11)
AND VARIABLE_3 IN (p)
)

UNION

SELECT DISTINCT Count (*) UID_XID AS 'Interactors' FROM [TABLE NAME] WHERE
VARIABLE_1 IN ('ASDQ6KJ8un4Q')
AND VARIABLE_2 IN (11)
AND UID_XID IN (
SELECT DISTINCT UID_XID FROM [TABLE NAME] WHERE
VARIABLE_1 IN ('ASDQ6KJ8un4Q')
AND VARIABLE_2 IN (21)
AND VARIABLE_3 IN (q)
)

ORDER BY UID_XID DESC
GO

Result:
ORDER BY items must appear in the select list if the statement contains a UNION, INTERSECT or EXCEPT operator.

Where do I need to put the ORDER BY? I tried in the beginning select statement, and nada.

Also, is there a way implicitly tell SQL SERVER to give me the results of a series of UNION queries with subqueries as I write them, not in ascending nor descending order?

Help?

Thanks!
Saul

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2012-01-26 : 15:18:52

The order by is in the correct place. The error is telling you that you need to add the column UID_XID to the select (or if it's there, remove the aliases).

btw, looks like you're actually missing commas in the select:
SELECT DISTINCT Count (*) UID_XID AS 'Interactors'

That needs a , after the Count(*). You're also going to get errors about columns appearing in the select but not in the group by.

p.s. Also, you don't need DISTINCT when you have UNION, UNION implies distinct. You also don't need DISTINCT inside an IN subquery.

--
Gail Shaw
SQL Server MVP

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2012-01-26 : 15:26:16

and... all your IN predicates are for single values, so they should be = rather than IN

I don't know if the execution engine is smart enough to optimise them out for you anyway.....

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

saulfeliz
Starting Member

14 Posts

Posted - 2012-01-26 : 15:46:43

Thanks for the reply, Gail.

I didn't know that you don't need to repeat the Distincts with UNIONS. Thanks :)

But I tried removing the aliases, and it still gives me the same error. Also, when I put a comma where you suggested, I get red squiggly lines, which I know = error coming.

So...can I tell SQL Server to give me results as I write them? If not, the Desc will suffice, but that's not working either.

Query now looks like this:

SELECT DISTINCT Count (*) UID_XID AS 'Interactors' FROM [TABLE NAME] WHERE
VARIABLE_1 IN ('ASDQ6KJ8un4Q')
AND VARIABLE_2 IN (11)
AND UID_XID IN (
SELECT UID_XID FROM [TABLE NAME] WHERE
VARIABLE_1 IN ('ASDQ6KJ8un4Q')
AND VARIABLE_2 IN (11)
AND VARIABLE_3 IN (p)
)
UNION
SELECT Count (*) UID_XID AS 'Interactors' FROM [TABLE NAME] WHERE
VARIABLE_1 IN ('ASDQ6KJ8un4Q')
AND VARIABLE_2 IN (11)
AND UID_XID IN (
SELECT UID_XID FROM [TABLE NAME] WHERE
VARIABLE_1 IN ('ASDQ6KJ8un4Q')
AND VARIABLE_2 IN (21)
AND VARIABLE_3 IN (q)
)

ORDER BY UID_XID DESC
GO

quote:
Originally posted by GilaMonster

The order by is in the correct place. The error is telling you that you need to add the column UID_XID to the select (or if it's there, remove the aliases).

btw, looks like you're actually missing commas in the select:
SELECT DISTINCT Count (*) UID_XID AS 'Interactors'

That needs a , after the Count(*). You're also going to get errors about columns appearing in the select but not in the group by.

p.s. Also, you don't need DISTINCT when you have UNION, UNION implies distinct. You also don't need DISTINCT inside an IN subquery.

--
Gail Shaw
SQL Server MVP

Thanks!
Saul

X002548
Not Just a Number

15586 Posts

Posted - 2012-01-26 : 15:48:41

is [TABLE_NAME] all the same or are they different?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

saulfeliz
Starting Member

14 Posts

Posted - 2012-01-26 : 16:08:31

They're all the same Brett.

quote:
Originally posted by X002548

is [TABLE_NAME] all the same or are they different?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

Thanks!
Saul

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2012-01-26 : 16:23:46

That query, as currently written, is full of errors.

SELECT Count (*) UID_XID AS 'Interactors' - Is UID_XID a column name or an alias for the count? If a column, you need a comma after the Count(*). If an alias for the count, you need to remove the AS 'Interactors', but you must do one, that line is syntactically invalid as it stands.
You do not need DISTINCT with union. Take the Distincts out, Union means Distinct the resultset after concatenation
You are going to get an error about having aggregations, columns without aggregations and no group by because of the Count(*) and the unaggregated UID_XID

Fix the errors first, then worry about the details of what order you want the results in. Currently there is no way that will run and I can't even work out what it is you want.

--
Gail Shaw
SQL Server MVP

saulfeliz
Starting Member

14 Posts

Posted - 2012-01-26 : 16:30:47

I don't know what to tell'ya Gail. When I do what you suggest, I get errors. When I leave it as is, it works.

But thanks anyway.

quote:
Originally posted by GilaMonster

That query, as currently written, is full of errors.

SELECT Count (*) UID_XID AS 'Interactors' - Is UID_XID a column name or an alias for the count? If a column, you need a comma after the Count(*). If an alias for the count, you need to remove the AS 'Interactors', but you must do one, that line is syntactically invalid as it stands.
You do not need DISTINCT with union. Take the Distincts out, Union means Distinct the resultset after concatenation
You are going to get an error about having aggregations, columns without aggregations and no group by because of the Count(*) and the unaggregated UID_XID

Fix the errors first, then worry about the details of what order you want the results in. Currently there is no way that will run and I can't even work out what it is you want.

--
Gail Shaw
SQL Server MVP

X002548
Not Just a Number

15586 Posts

Posted - 2012-01-26 : 16:40:09

Is it bigger than a breadbox?

Is it SQL Server?

Version 2008 R2?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

saulfeliz
Starting Member

14 Posts

Posted - 2012-01-26 : 16:43:18

Yeap, SQL SERVER 2008 R2.
It's about the same size as a bread box. Maybe a bit smaller.

quote:
Originally posted by X002548

Is it bigger than a breadbox?

Is it SQL Server?

Version 2008 R2?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

Thanks!
Saul

X002548
Not Just a Number

15586 Posts

Posted - 2012-01-26 : 16:47:42

????

Assuming VARIABLE_3 is char?


	SELECT Count(*) AS CNT, UID_XID AS 'Interactors' 
	  FROM [TABLE NAME] 
	 WHERE VARIABLE_1 ='ASDQ6KJ8un4Q'
	   AND VARIABLE_2 = 11
	   AND VARIABLE_3 = 'p'  -- Will Do the Same thing as Your IN
/* Makes NO Sense
	   AND UID_XID IN (SELECT DISTINCT UID_XID 
						 FROM [TABLE NAME] 
						WHERE VARIABLE_1 = 'ASDQ6KJ8un4Q'
						  AND VARIABLE_2 = 11
						  AND VARIABLE_3 = 'p')
*/

UNION
	SELECT Count(*) AS CNT, UID_XID AS 'Interactors' 
	  FROM [TABLE NAME] 
	 WHERE VARIABLE_1 ='ASDQ6KJ8un4Q'
	   AND VARIABLE_2 = 11
	   AND UID_XID IN (
				SELECT DISTINCT UID_XID 
				  FROM [TABLE NAME] 
				 WHERE VARIABLE_1 IN ('ASDQ6KJ8un4Q')
				   AND VARIABLE_2 IN (21)
				   AND VARIABLE_3 IN ('q'))

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

saulfeliz
Starting Member

14 Posts

Posted - 2012-01-26 : 16:55:37

Assume all variables are CHAR
How do I Order by?
Don't focus on whether to use In or =, that's not the major issue. The issue I'm having is how to tell it to Order by DESC or as I have it written.

Is it that SQL won't return results as they are written?

Here's the query as it stands now:

SELECT DISTINCT Count UID_XID AS 'Interactors' FROM [TABLE NAME] WHERE
VARIABLE_1 IN ('ASDQ6KJ8un4Q')
AND VARIABLE_2 IN (11,'w')
AND UID_XID IN (
SELECT UID_XID FROM [TABLE NAME] WHERE
VARIABLE_1 IN ('ASDQ6KJ8un4Q')
AND VARIABLE_2 IN (11,'f')
AND VARIABLE_3 IN ('p','Q')
)

UNION

SELECT Count UID_XID AS 'Interactors' FROM [TABLE NAME] WHERE
VARIABLE_1 IN ('ASDQ6KJ8un4Q')
AND VARIABLE_2 IN (11,33)
AND UID_XID IN (
SELECT UID_XID FROM [TABLE NAME] WHERE
VARIABLE_1 IN ('ASDQ6KJ8un4Q')
AND VARIABLE_2 IN (21,34)
AND VARIABLE_3 IN ('q','X')
)

GO

quote:

Originally posted by X002548

????

Assuming VARIABLE_3 is char?


	SELECT Count(*) AS CNT, UID_XID AS 'Interactors' 
	  FROM [TABLE NAME] 
	 WHERE VARIABLE_1 ='ASDQ6KJ8un4Q'
	   AND VARIABLE_2 = 11
	   AND VARIABLE_3 = 'p'  -- Will Do the Same thing as Your IN
/* Makes NO Sense
	   AND UID_XID IN (SELECT DISTINCT UID_XID 
						 FROM [TABLE NAME] 
						WHERE VARIABLE_1 = 'ASDQ6KJ8un4Q'
						  AND VARIABLE_2 = 11
						  AND VARIABLE_3 = 'p')
*/

UNION
	SELECT Count(*) AS CNT, UID_XID AS 'Interactors' 
	  FROM [TABLE NAME] 
	 WHERE VARIABLE_1 ='ASDQ6KJ8un4Q'
	   AND VARIABLE_2 = 11
	   AND UID_XID IN (
				SELECT DISTINCT UID_XID 
				  FROM [TABLE NAME] 
				 WHERE VARIABLE_1 IN ('ASDQ6KJ8un4Q')
				   AND VARIABLE_2 IN (21)
				   AND VARIABLE_3 IN ('q'))

Thanks!
Saul

saulfeliz
Starting Member

14 Posts

Posted - 2012-01-26 : 17:10:29

OK guys. I posted in another forum and got the answer. Apparently all I had to do was ORDER BY 'ALIAS', instead of the original name of the column.

Thanks anyway!
Saul

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2012-01-26 : 17:16:53

quote:
Originally posted by saulfeliz

I don't know what to tell'ya Gail. When I do what you suggest, I get errors. When I leave it as is, it works.

When I take exactly what you posted and do a syntax check, I get this:

Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'AS'.
Msg 156, Level 15, State 1, Line 11
Incorrect syntax near the keyword 'AS'.

If I fix those errors I get

Msg 207, Level 16, State 1, Line 8
Invalid column name 'p'.
Msg 207, Level 16, State 1, Line 18
Invalid column name 'q'.

If I fix those I get

Msg 8120, Level 16, State 1, Line 1
Column 'TABLE NAME.UID_XID' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

That's on SQL 2008.

So my guess is that you're not posting exactly what you are running, which really limits the amount of help we can give you.

--
Gail Shaw
SQL Server MVP

saulfeliz
Starting Member

14 Posts

Posted - 2012-01-26 : 17:36:27

I copy/pasted the same things.
I'm using SQL SERVER 2008 R2, so maybe that's it?

Issued solved now. But thanks anyway.

quote:
Originally posted by GilaMonster

quote:
Originally posted by saulfeliz

I don't know what to tell'ya Gail. When I do what you suggest, I get errors. When I leave it as is, it works.

When I take exactly what you posted and do a syntax check, I get this:

Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'AS'.
Msg 156, Level 15, State 1, Line 11
Incorrect syntax near the keyword 'AS'.

If I fix those errors I get

Msg 207, Level 16, State 1, Line 8
Invalid column name 'p'.
Msg 207, Level 16, State 1, Line 18
Invalid column name 'q'.

If I fix those I get

Msg 8120, Level 16, State 1, Line 1
Column 'TABLE NAME.UID_XID' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

That's on SQL 2008.

So my guess is that you're not posting exactly what you are running, which really limits the amount of help we can give you.

--
Gail Shaw
SQL Server MVP

Thanks!
Saul

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2012-01-26 : 17:38:56

R2 is near-identical to 2008 and the syntax of the two is absolutely identical.

--
Gail Shaw
SQL Server MVP

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources