Select query in a select 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 2000 Forums

Transact-SQL (2000)

Select query in a select query

Author

Topic

Champinco
Yak Posting Veteran

54 Posts

Posted - 2006-11-20 : 01:52:34

Hi guys was wondering how i would go about 'joining' these two queries together such that I get the same result as the end of 2nd one.

1st query:
SELECT commission_summary.distributor as Distributor,
commission_summary.commission_month as Commission_Date,
sum(commission_summary.payment_this_period_carried_forward) as Total
--into Top_20_Dealers
from commission_summary
group by commission_summary.commission_month, distributor
order by commission_summary.commission_month desc, sum(commission_summary.payment_this_period_carried_forward) desc

2nd query:

--Return top 20 values
select datepart(year, Top_20_Dealers.Commission_Date) [year],
datename(month, Top_20_Dealers.Commission_Date) monthname,
Top_20_Dealers.distributor,
'$' + convert(varchar(50),Top_20_Dealers.Total,1)
from Top_20_Dealers
where Top_20_Dealers.Total in
(select top 20 e.Total
from Top_20_Dealers e
where datepart(year, e.Commission_Date)
= datepart(year, Top_20_Dealers.Commission_Date) and datepart(month, e.Commission_Date)
= datepart(month, Top_20_Dealers.Commission_Date))
order by datepart(year, Top_20_Dealers.Commission_Date),
datepart(month, Top_20_Dealers.Commission_Date),
Top_20_Dealers.Total desc

As you can see the way i have structured these two queries is such that it is done in two steps: i.e: runs the 1st query, then saves it to another table, then the 2nd query runs based on the saved table from the 1st query.
Is there somehow i can do both in one hit...i don't have to have the saved table in the 1st query.

Thanks
GK

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-11-20 : 02:04:45

If results of 1st query are not too great, you can use 1st query as derived table and use it in second query.

Otherwise, you can save the results of 1st query to the table variable and use that table variable in second query.

Harsh Athalye
India.
"Nothing is Impossible"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources