Sorting Query Results

Suppose I want to see just the names of the investigators from the University of Colorado at Denver who have been awarded NSF grants. Scanning the result set for familiar names would be much easier if the results were returned sorted by the person's name. The ORDER BY clause of the SELECT statement allows you to specify the column or columns on which to sort the result set. The syntax is given below.

select_stmt:
          SELECT [DISTINCT] select_item [, select_item]...
          FROM table_ref [, table_ref]... [WHERE conditional_expr] sorting

sorting:
          ORDER BY sort_col [ASC | DESC][, sort_col [ASC | DESC]]...

sort_col:
          num | column_var

The num is the ordinal position of the SELECT expression on which to sort where num = 1 refers to the first expression. The column_name is either the specified column_name or the name of a column from table_name or, if not specified, the first table referenced in the FROM clause which contains a column of the same name. The default sort order is ASC (ascending) but DESC can be specified to reverse the order. If more than one ORDER BY column is specified each subsequent column specifies a sort order within each value from the outer sort column(s). If SELECT DISTINCT is specified, duplicate rows in the result set will be eliminated. All of this is actually easier to show than to explain.

The next query will return the list of all investigators from the University of Colorado Denver that have been awarded NSF grants.

select person.name 
    from award natural join investigator natural join person 
 		join sponsor on (sponsor_nm = sponsor.name) 
 			where sponsor.name = "U of Colorado Denver" 
 			order by 1;

PERSON.NAME
Alaghband, Gita
Altman, Tom
Andrew., Andrew
Andrew., Andrew
Andrew., Andrew
Andrew., Andrew
Banks, David L.
Beekman, Christopher S.
Beekman, Christopher S.
    ...
Stith, Bradley J.
Stith, Bradley J.
Stith, Bradley J.
Tagg, Randall P.
Tagg, Randall P.
Tagg, Randall P.
Tang, Michael S.
Tracer, David P.
Walker, Kenneth
Weaver, Gabriela C.
Weaver, Gabriela C.
Weaver, Gabriela C.
Zamudio, Stacy
Zapien, Donald C.

This list includes some duplicate entries. To eliminate them add DISTINCT to the SELECT as shown below.

select distinct person.name 
    from award natural join investigator natural join person 
 		join sponsor on (sponsor_nm = sponsor.name) 
 			where sponsor.name = "U of Colorado Denver" 
 			order by 1;

PERSON.NAME
Alaghband, Gita
Altman, Tom
Andrew., Andrew
Banks, David L.
Beekman, Christopher S.
Bennethum, Lynn S.
Billups, Stephen C.
	...
Stith, Bradley J.
Tagg, Randall P.
Tang, Michael S.
Tracer, David P.
Walker, Kenneth
Weaver, Gabriela C.
Zamudio, Stacy
Zapien, Donald C.

The next example will show the list of awards for each investigator in order of when the grant was issued with the most recent listed first.

select person.name, award_date, title 
 	from award natural join investigator natural join person
   		join sponsor on (sponsor_nm = sponsor.name)
 			where sponsor.name = "U of Colorado Denver"
 			order by 1, 2 desc;

PERSON.NAME              AWARD_DATE TITLE
Alaghband, Gita          1993-08-16 RIA: Parametric Modeling Tools for Performance 
Altman, Tom              1992-09-04 Elimination of Certain Ambiguity Causing Constru..
Andrew., Andrew          2002-08-28 Preconditioned Algorithms for Large Eigenvalue 
Andrew., Andrew          2002-07-30 Sixth IMACS International Symposium on Iterative 
Andrew., Andrew          2000-08-28 Acquisition of a High-Performance Parallel Compu..
Andrew., Andrew          1995-06-26 Mathematical Sciences: Preconditioned Parallel 
Banks, David L.          1998-09-11 Group Travel Award to Support U.S Participation in 
Beekman, Christopher S.  2002-11-06 The Articulation of Political Strategies and Reg..
Beekman, Christopher S.  2002-06-12 The Articulation of Political Strategies and Reg.. ...
Stein, Fredrick M.       2002-01-28 Energy 2020: A Teacher Enhancement Workshop To 
Stith, Bradley J.        2002-04-30 Lipid Signaling During Fertilization
Stith, Bradley J.        1999-03-22 RUI: Lipid Signaling During Fertilization
Stith, Bradley J.        1996-05-15 RUI: Induction of Cell Division by Protein Kinas..
Tagg, Randall P.         2002-01-28 Energy 2020: A Teacher Enhancement Workshop To 
Tagg, Randall P.         1995-06-30 Course Modules in Apparatus Design and Experime..
Tagg, Randall P.         1995-06-08 Mathematical Sciences: Patterns, Chaos, and ..
Tang, Michael S.         1995-02-02 Engineering, Technology and Culture: with an Em..
Tracer, David P.         1999-12-20 Breast Feeding Structure and Parental Investment..
Walker, Kenneth          1995-02-02 Engineering, Technology and Culture: with an Em..
Weaver, Gabriela C.      2002-01-28 Energy 2020: A Teacher Enhancement Workshop To 
Weaver, Gabriela C.      1999-12-14 Proof of Concept Proposal for Physical Chemistry..
Weaver, Gabriela C.      1996-05-10 Integration of Novel Laser-Spectroscopy Experim..
Zamudio, Stacy           2002-07-17 Ancestry, Altitude and Placental Development in 
Zapien, Donald C.        2002-02-11 RUI: Investigation of the Relationship of Ferri..