Querying against the results of a Query

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)

Querying against the results of a Query

Author

Topic

Firebrand
Starting Member

24 Posts

Posted - 2010-06-24 : 09:10:14

Hello everyone,

I have a fairly complex Query that is now successfully returning a set of data.

I'd like to be able Query against that set of results as if it where a single table.

So I'm thinking that I'm going to need to DECLARE my original query as a table before querying against it. I've seen this done in the past and I'm sure it's possible.

Any pointers would be appreciated.

Many thanks

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-06-24 : 09:11:46

select * from
(here comes your select) as dt
where ...

No, you're never too old to Yak'n'Roll if you're too young to die.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-06-24 : 09:12:54

select columns from
(
your_complex_query
) as t
where some_col='some value'

Madhivanan

Failing to plan is Planning to fail

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-06-24 : 09:13:21

Madhivanan

Failing to plan is Planning to fail

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-06-24 : 09:13:24

It is called a DERIVED TABLE and in this you must have a column name for each column in the select list.
i.e. select max(id) errors and select max(id) as id errors not.

No, you're never too old to Yak'n'Roll if you're too young to die.

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2010-06-24 : 09:43:57

You couild just insert your query results into a table, temp or permanant, and query that table.

CODO ERGO SUM

Firebrand
Starting Member

24 Posts

Posted - 2010-06-24 : 09:46:07

That's great! Thanks very much everyone.

Am I right in saying that if I'm using a Derived table within a Stored Procedure that the Derived table will not be cached as pasrt of the execution plan?

Many thanks

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-06-24 : 09:46:57

CTE is also possible

No, you're never too old to Yak'n'Roll if you're too young to die.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-06-24 : 09:56:13

quote:
Originally posted by Firebrand

That's great! Thanks very much everyone.

Am I right in saying that if I'm using a Derived table within a Stored Procedure that the Derived table will not be cached as pasrt of the execution plan?

Many thanks

I think so.

No, you're never too old to Yak'n'Roll if you're too young to die.

Firebrand
Starting Member

24 Posts

Posted - 2010-06-24 : 09:57:02

So what's the benefit of using a Common Table Expression over a Derived or Temporary table?

Thanks

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-06-24 : 10:04:32

Nothing.
A CTE can be used recursive but that is another story...

No, you're never too old to Yak'n'Roll if you're too young to die.

Firebrand
Starting Member