Click here to Skip to main content
15,035,723 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
Hi,
Can anyone please give me the query to find students of the departments that has maximum count.I have tblstudents table where i have columns stuid,stuame,deptno. Please help me in writing this query. I have tried the following query:

SQL
select * 
from emp 
where deptno =(select deptno 
               from (select count(e.empno)as counts,e.deptno 
                     from emp e group by deptno) a
                     where a.counts=(select MAX(counts)  
                                     from a) );


But ended up with a error:Invalid object name 'a'.

Regards
Chaithanya M
Posted
Updated 28-May-11 3:22am
v4
Comments
OriginalGriff 28-May-11 5:56am
   
You will have to expand on that: how do students relate to employees, for example?
What have you tried?
M.CHAITHANYA 28-May-11 6:10am
   
Hi,
I am sorry ,i have mistyped tblemployee .it is actually tblstudents.. I tried the following:

select * from emp where deptno =(select deptno from
(select count(e.empno)as counts,e.deptno from emp e group by deptno) a
where a.counts=(select MAX(counts) from a) );

But i ended up with an error:invalid object name 'a'.
Kim Togo 28-May-11 7:35am
   
Please use "Improve question" instead of posting your SQL statement as a comment. It is much easier to read.
Sandeep Mewara 28-May-11 5:57am
   
Tried anything?

1 solution

I wont be able to give you the specific answer because i want you to learn LINQ
so here you go
Click Here
   

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900