4.5. Using ORDER BY with a Join

As with other SELECT statements, the ORDER BY clause can be used in joins to order the result set. For example, to order the result set (output) of one of the queries presented earlier in this chapter by the course_number column, we would type the following:

SELECT c.course_name, c.course_number, c.credit_hours, c.offering_dept, p.prereq
FROM Course c JOIN Prereq p
ON c.course_number=p.course_number
ORDER BY c.course_number

Or this alternative:

SELECT c.course_name, c.course_number, c.credit_hours, c.offering_dept, p.prereq
FROM Course c JOIN Prereq p
ON c.course_number=p.course_number
ORDER BY 2

ORDER BY 2 means to order by the second column of the result set. This query produces the same 12 rows as the previous query, but ordered alphabetically in the order of course_number:

COURSE_NAME          COURSE_NUMBER CREDIT_HOURS OFFERING_DEPT PREREQ
-------------------- ------------- ------------ ------------- --------
MANAGERIAL FINANCE   ACCT3333      3         ACCT          ACCT2220
ORGANIC CHEMISTRY    CHEM3001      3         CHEM          CHEM2001
DATA STRUCTURES      COSC3320      4         COSC          COSC1310
DATABASE             COSC3380      3         COSC          COSC3320
DATABASE             COSC3380      3         COSC          MATH2410
ADA - INTRODUCTION   COSC5234      4         COSC          COSC3320
ENGLISH COMP II      ENGL1011      3         ENGL          ENGL1010
FUND. TECH. WRITING  ENGL3401      3         ENGL          ENGL1011
WRITING FOR NON MAJO ENGL3520      2         ENGL          ENGL1011
MATH ANALYSIS        MATH5501      3         MATH          MATH2333
AMERICAN GOVERNMENT  POLY2103      2         POLY          POLY1201
POLITICS OF CUBA     POLY5501      4         POLY          POLY4103
 
(12 row(s) affected)