Manipulating Temporary Tables

Question

Post reply

Manipulating Temporary Tables

Shannon Whalen

SSC Rookie

Points: 25
More actions
September 24, 2006 at 8:48 pm

#367446

Hi all! I am new to SQL Server and have a slight problem. I am trying to select from a nested query, although by the time I get to my where condition, it doesn't recognize the table name. My code is below:
select temp.cname from (select c.cname,count(o.cno) AS total
from customers c,orders o where
o.cno = c.cno
group by c.cname) AS temp
where temp.total =(select max(temp.total)from temp)
When I run this query, I get this error:
Msg 208, Level 16, State 1, Line 1
Invalid object name 'temp'.
But, if I run this it works:
select temp.cname from (select c.cname,count(o.cno) AS total
from customers c,orders o where
o.cno = c.cno
group by c.cname) AS temp
where temp.total = 2
This tells me that last query is out of bounds for the table temp, but how would I write it to get it IN bounds for temp?
I have searched up the problem and all the solutions that I have found are of the format of the first query, only I can't quite get it to work.
I have tried this on MS SQL Server 2000 and 2005.
Thanks in advance for your help!
Shannon

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

ChiragNS One Orange Chip Points: 26137 More actions · Answer 1

hi

try this

select

temp.cname from (select c.cname,count(o.cno) AS total from customers c,orders o where o.cno = c.cnogroup by c.cname) AS temp where temp.total =(select max(temp.total)from (select c.cname,count(o.cno) AS total from customers c,orders o where o.cno = c.cno group by c.cname) AS temp )

or if u r using a stored procedure

put the results of the query that forms the table "temp" in a temporary table and then query the data from the temporary table

Chirag

"Keep Trying"

Shannon Whalen SSC Rookie Points: 25 More actions · Answer 2

That works so, I can use it. Thought there might be a more elegant solution.

Thanks for the reply.

vadba SSChampion Points: 11133 More actions · Answer 3

Are you trying to select the customer name with the most orders?

If so, here is another way:

SELECT TOP 1 temp.cname

FROM (SELECT c.cname

, Count(o.cno) AS total

FROM customers c

JOIN orders o

ON c.cno = o.cno

GROUP BY c.cname

) AS temp

ORDER BY temp.total DESC

Shannon Whalen SSC Rookie Points: 25 More actions · Answer 4

Addict,

That is the way I ended up doing it. However, that just gives the top name with the most orders. What if I have 5 people who have the same number of orders? I want all of their names not just the one customer who happens to end up at the top of the list.

Thanks for the reply,

Shannon