How do I find the top 3 values? 1

[jisque]

I've got two tables, with these sample data, I'm working with.I wish to find the courses with the with the top three numbers of enrollments. Any help with this problem appreciated.

Students:
Student_id Student_name Course_code
s01 Bell C103
s02 Robinson C102
s03 Edmonds C107
s04 Anarkali C103
s05 Pettit C102
s06 Smith C104
s07 Tennent C105
s08 James C104
s09 Kay C103
s10 Jones C104
s11 Benyon C103
s12 Hook C102
s13 Woodward C104
s14 Bird C103
s16 Samuel C107
s19 Cumberland C105


Course_code Course_name Number_of_places
C102 Beginers 20
C103 Painting 15
C104 Still Life 20
C105 Cartooning 15
C107 Water Colours 12

 

[Golom]

Try

Select TOP 3 C.Course_Code, C.Course_Name, Count(*) As Enrollment

From Students S INNER JOIN Courses C
     ON S.Course_Code = C.Course_Code

Group By C.Course_Code, C.Course_Name

Order By 3 DESC

 

[fordtran]

What is the signifcance of the 3 in the 'order by 3' - does it refer to the number of rows or to the number of the field in the table - or to what ?

Thanks


fordtran

 

[lespaul]

the '3' in the ORDER BY clause indicates the third field in the select clause, in this case the Count field.


Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases: The Fundamentals of Relational Database Design