Click here to Skip to main content
15,867,834 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 20-Jan-22 23:56pm
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?

I wont be able to give you the specific answer because i want you to learn LINQ
so here you go
Click Here
 
Share this answer
 
SELECT * FROM(SELECT DEPARTMENT_NAME,COUNT(STUDENT_ID)FROM DEPARTMENT D JOIN STUDENT S ON D.DID=S.DID GROUP BY DEPARTMENT_NAME ORDER BY 2 DESC)WHERE ROWNUM=1;
 
Share this answer
 
Comments
CHill60 21-Jan-22 12:45pm    
Reasons for my downvote
1. The ORDER BY in your subquery is not valid unless TOP, OFFSET or FOR XML is also specified
2. Incorrect syntax near the keyword 'WHERE' because you have not given your sub-query an alias. I use "Temp" as an alias to progress
3. No column name was specified for column 2 of 'TEMP'. because you have not given the count part of your sub-query a column name
4. Invalid column name 'ROWNUM'. There is no ROWNUM here
5. You are using Department and Student instead of Employee

If this was meant to be a question about what was wrong with your code then you should have used the red "Ask a Question" link at the top of this page.

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