Author |
Topic |
Prosercunus
Starting Member
22 Posts |
Posted - 2012-10-11 : 23:04:27
|
Using select statements find the name(s) and sid(s) of the student(s) enrolled in the most classes.So far I have...select Student.sid, sname, Major.dname from Student, MajorI now need to find those students that are enrolled in the most classes. I can see all of the classes they are in, just unsure what statement to make it show it only shows the students enrolled in the most amount of classes or (dname).Here are the two tables.Major(dname,sid)Student(sid,sname,sex,age,year,qpa) |
|
sql-programmers
Posting Yak Master
190 Posts |
Posted - 2012-10-12 : 00:33:01
|
Hi,Try this,DECLARE @Major AS TABLE (dname VARCHAR(50),SID INT )DECLARE @Student AS TABLE (SID INT IDENTITY,sname VARCHAR(50),sex CHAR(1),age INT,[year] INT)INSERT INTO @Student (sname, sex, age, [year]) VALUES ('A', 'M', 12, 1999)INSERT INTO @Student (sname, sex, age, [year]) VALUES ('B', 'M', 13, 2000)INSERT INTO @Student (sname, sex, age, [year]) VALUES ('C', 'F', 13, 2000)INSERT INTO @Student (sname, sex, age, [year]) VALUES ('D', 'M', 15, 1999)INSERT INTO @Student (sname, sex, age, [year]) VALUES ('E', 'F', 16, 1999)INSERT INTO @Major ( dname, SID )VALUES ( 'D 1', 1)INSERT INTO @Major ( dname, SID )VALUES ( 'D 2', 1)INSERT INTO @Major ( dname, SID )VALUES ( 'D 3', 1)INSERT INTO @Major ( dname, SID )VALUES ( 'D 1', 2)INSERT INTO @Major ( dname, SID )VALUES ( 'D 2', 2)INSERT INTO @Major ( dname, SID )VALUES ( 'D 1', 3)INSERT INTO @Major ( dname, SID )VALUES ( 'D 2', 4)INSERT INTO @Major ( dname, SID )VALUES ( 'D 3', 5)SELECT M.SID, COUNT(*) FROM @Student SINNER JOIN @Major M ON s.SID = m.SIDGROUP BY M.SID HAVING COUNT(*) > 1 SELECT S.SID, S.sname, M.dname FROM @Student SINNER JOIN (SELECT M.SID, COUNT(*) AS Cnt FROM @Student SINNER JOIN @Major M ON s.SID = m.SIDGROUP BY M.SID HAVING COUNT(*) > 1) AS M1 ON m1.SID = s.sidINNER JOIN @Major M ON M1.SID = m.SIDSQL Server Programmers and Consultantshttp://www.sql-programmers.com/ |
|
|
bandi
Master Smack Fu Yak Hacker
2242 Posts |
Posted - 2012-10-12 : 00:36:48
|
Hi,Alternate Solution is:;with cte as (SELECT sid, dname, count(dname) over(Partition by sid) noOfDepts from Major)select S.*, dname, noOfDeptsFROM student sJOIN cte m ON s.sid = m.sidWHERE noOfDepts = (SELECT MAX(noOfDepts) FROM cte)--Chandu |
|
|
|
|
|