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

Manipulating Temporary Tables

Cookies on SQLServerCentral