How do I find the top 3 values? 2

[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

 

[bborissov]

I am not sure if that is what you want:

CREATE TABLE #Test (Student_id  Char(3),Student_name varchar(50), Course_code char(4))
INSERT INTO #Test SELECT 's01','Bell','C103' UNION ALL
                  SELECT 's02','Robinson','C102' UNION ALL
				  SELECT 's03','Edmonds','C107' UNION ALL
                  SELECT 's04','Anarkali','C103' UNION ALL
                  SELECT 's05','Pettit','C102' UNION ALL
                  SELECT 's06','Smith','C104' UNION ALL
                  SELECT 's07','Tennent','C105' UNION ALL
                  SELECT 's08','James','C104' UNION ALL
                  SELECT 's09','Kay','C103' UNION ALL
                  SELECT 's10','Jones','C104' UNION ALL
                  SELECT 's11','Benyon','C103' UNION ALL
                  SELECT 's12','Hook','C102' UNION ALL
                  SELECT 's13','Woodward','C104' UNION ALL
                  SELECT 's14','Bird','C103' UNION ALL
                  SELECT 's16','Samuel','C107' UNION ALL
                  SELECT 's19','Cumberland','C105'

SELECT TOP 3 Course_code, Cnt FROM
(SELECT Course_code, COUNT(*) AS Cnt FROM #test GROUP BY Course_code) AS Test
ORDER BY Cnt DESC
DROP TABLE #test

Borislav Borissov

 

[jbenson001]

How about:

select c.Course_Code, c.Course_Name, Enrolled = Count(c.Course_Code)
from students s
   inner join courses c
      ON s.course_code = c.course_code
group by c.Course_Code, c.Course_Name
order by c.course_code

Jim

 

[jisque]

Thanks a lot, but is there a way of doing this without using the Top function? I am actually using something called an SQL Anywhere database, and it does not seem to recognise The Top function.

 

[jbenson001]

Sorry should be:

select top 3 c.Course_Code, c.Course_Name, Enrolled = Count(c.Course_Code)
from students s
   inner join courses c
      ON s.course_code = c.course_code
group by c.Course_Code, c.Course_Name
order by c.enrolled desc

 

[vongrunt]

SQLAnywhere (Sybase)? Not ASA?

I guess you'll have to use NUMBER() function and temp table... not sure though, haven't used SQLAnywhere since '2000

------
Theory: everybody knows everything, nothing works
Practice: everything works, nobody knows why