CODEWITHSUNDEEP
sql
mrsw
mrsw

Reputation: 175

How to retrieve results for top items

I have ran a query to give me the total number of students within each school but now I need to know the name of those students within each school while keeping the top result by total number at the top. How can I add to this query to show me the names of the students?

Here is what I have to show me the total number of students at each school: 

SELECT 
   dbo_Schools.Schools, 
   Count(dbo_tStudent.Student) AS NumberOfStudents
FROM 
   dbo_tStudent 
      INNER JOIN dbo_tSchools ON dbo_tStudent.SchoolID=dbo_tSchool.SchoolID
GROUP BY dbo_tSchool.School
ORDER BY Count(dbo_tStudent.Student) DESC;

Its important that I keep the schools in order from top number of students while listing the students

Upvotes: 3

Views: 82

Answers (2)

ovm
ovm

Reputation: 2532

Assuming that you are using SQL Server, you can use a CTE to join the first aggregate with the details like this:

;WITH cte as (
    SELECT TOP 1000 dbo_Schools.SchoolID, Count(dbo_tStudent.Student) AS NumberOfStudents
    FROM 
        dbo_tStudent 
            INNER JOIN dbo_tSchools ON dbo_tStudent.SchoolID = dbo_tSchools.SchoolID
    GROUP BY dbo_tSchool.School
    ORDER BY Count(dbo_tStudent.Student) DESC
)

SELECT
    sc.<your school name column>,
    st.<your student columns>
from
    dbo_tStudent st
        INNER JOIN cte ON st.SchoolID = cte.SchoolID
        INNER JOIN dbo_tSchools sc on cte.SchoolID = sc.SchoolID

More generally speaking: you need a derived table (your aggregation containing the group by clause) that is joined with the select statement for the student details. In this example, the CTE basically is a SQL Server feature that facilitates the use of derived tables.

Upvotes: 0

SoulTrain
SoulTrain

Reputation: 1904

In this case you could use a Sub Query to achieve your resultset.

To use order by inside a subquery, you will also need a top or limit operator.

SELECT sc.schoolname
    ,st.columns...
FROM dbo_tStudent st
INNER JOIN (
    SELECT TOP 1000 dbo_Schools.SchoolID
        ,min(schoolname) schoolname
        ,Count(dbo_tStudent.Student) AS NumberOfStudents
    FROM dbo_tStudent
    INNER JOIN dbo_tSchools ON dbo_tStudent.SchoolID = dbo_tSchools.SchoolID
    GROUP BY dbo_tSchool.School
    ORDER BY Count(dbo_tStudent.Student) DESC
    ) sc ON st.SchoolID = sc.SchoolID

Upvotes: 1

Related Questions