GROUP BY on 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)

GROUP BY on subqueries

Author

Topic

Andraax
Aged Yak Warrior

790 Posts

Posted - 2005-01-20 : 09:20:10

Hello guys. I just got a question from a colleague, I will clarify with an example:

select (select 1) as c1, max(id)
from sysobjects
group by ?

The question mark shows my problem. Is it possible to group by the column c1? "group by c1" doesn't work, neither does "group by 1" or "group by (select 1)".

What I can think of is to put the subquery in a UDF, or reformulate and put it as a derived table in the from clause, but I would like to know first if it is possible or not.

Thanks in advance,
Andraax

LarsG
Constraint Violating Yak Guru

284 Posts

Posted - 2005-01-20 : 10:02:46

What are trying to do?


select 1 as c1, max(id)
from sysobjects


select c1,max(id) 
from (select id, (select ... ) as c1 from t) dt
group by c1

Andraax
Aged Yak Warrior

790 Posts

Posted - 2005-01-20 : 10:15:22

It's an example. The subquery will not look like that in the actual query. The question is: Is it possible to group by a subquery like that?

/Andraax

LarsG
Constraint Violating Yak Guru

284 Posts

Posted - 2005-01-20 : 11:08:35

No, not without using a derived table as in my second example.

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2005-01-20 : 13:40:01

I would suggest that if you think you need to do this, you should try re-writing your query in a way that doesn't. Chances are it will be very inefficient and the query was written kind of backwards. Why don't you post an example and we can help you re-write it more efficiently.

- Jeff

Andraax
Aged Yak Warrior

790 Posts

Posted - 2005-01-21 : 03:41:17

Hi!

As I stated before, this is a question I'm relaying from a colleague. It's merely a question about syntax, nothing more. I don't have a real world example and I have never felt the need to use such a method myself.

Thanks for the reply Lars. I wonder why they don't allow grouping on column numbers, as they do with ordering. But maybe it raises some issue or other which cannot be solved...

/Andraax

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-01-21 : 03:57:28

[code]
Select * from(
select (select 1) as c1, max(id) MaxId
from sysobjects) t
group by c1,MaxId
[/code]
Madhivanan

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources