Using Inner join and sorting records in descending order

I am working with two tables(student_class and class) in my database. I have a query below that shows class that have students. But it is not quite what I am looking for. How to display classes that have students but show the results so maximum seats is descending. Would count be needed?

SELECT 
    class.class_name 
FROM 
    class 
INNER JOIN 
    student_class ON class.class_id = student_class.class_id;

Tables:

Student_class:

 CLASS_ID STUDENT_ID
---------- ----------
         2         12
         2         11
         2          2
         7          5
         7          6
         7          7
         7          8
         7          9
         9          2
         9         11
         9         12
        10         20
        10          2
        10          4

Class:

  CLASS_ID CLASS_NAME          TEACHER_ID MAX_SEATS_AVAILABLE
---------- ------------------- ---------- -------------------
         1 Intro to ALGEBRA            11                  12
         2 Basic CALCULUS               2                  10
         3 ABC and 123                  1                  15
         4 Sharing 101                  8                  10
         5 Good Talk, Bad Talk          9                  20
         6 Nap Time                     1                  21
         7 WRITing 101                  5                  10
         8 Finger Painting              9                  14
         9 Physics 230                  2                  20
        10 Gym                          5                  25

Answer

Just use an order by statement:

SELECT class.class_name FROM class INNER JOIN student_class ON class.class_id = student_class.class_id
ORDER BY class.max_seats_available DESC

Advertisement

Answer