. SQL . SQL Order By

SQL Order By

SQL Order By Example One

Q: Who was in Blade Runner, Brazil, or The Matrix? Please show by film title. 
   
 Select Film_Title, Thespian_First_Name, Thespian_Last_Name
   From Thespian_Film_Table
  Where Film_Title in ("Blade Runner", "Brazil", "The Matrix")
  Order by Film_Title;

   
 Blade Runner,   Harrison,   Ford
 Blade Runner,   Rutger,     Hauer
 Blade Runner,   Sean,       Young
 Blade Runner,   Daryl,      Hannah
 Brazil,         Jonathan,   Pryce
 Brazil,         Robert,     DeNiro
 Brazil,         Michael,    Palin
 The Matrix,     Keanu,      Reeves

SQL Order By Example Two

Q: Please show by thespian last name now. 
   
 Select Film_Title, Thespian_First_Name, Thespian_Last_Name
   From Thespian_Film_Table
  Where Film_Title in ("Blade Runner", "Brazil", "The Matrix")
  Order by Thespian_Last_Name;

   
 Brazil,         Robert,     DeNiro
 Blade Runner,   Harrison,   Ford
 Blade Runner,   Daryl,      Hannah
 Blade Runner,   Rutger,     Hauer
 Brazil,         Michael,    Palin
 Brazil,         Jonathan,   Pryce
 The Matrix,     Keanu,      Reeves
 Blade Runner,   Sean,       Young

SQL Order By Example Three

Q: Now show the same thing in reverse order. 
   
 Select Film_Title, Thespian_First_Name, Thespian_Last_Name
   From Thespian_Film_Table
  Where Film_Title in ("Blade Runner", "Brazil", "The Matrix")
  Order by Thespian_Last_Name Descend;

   
 Blade Runner,   Sean,       Young
 The Matrix,     Keanu,      Reeves
 Brazil,         Jonathan,   Pryce
 Brazil,         Michael,    Palin
 Blade Runner,   Rutger,     Hauer
 Blade Runner,   Daryl,      Hannah
 Blade Runner,   Harrison,   Ford
 Brazil,         Robert,     DeNiro
Note: The default order is ascend, you must specify descend when you want descend.
Also Note: For some DBMSs you must specify desc instead of descend when you want descend.
Sign In
to add your own comment
Comment by TheMadProfessor Rate this Comment

Another option when using ORDER BY is to specify the result table column number instead of the field name. To demonstrate, your example # 2 could have been written as:

Select Film_Title, Thespian_First_Name, Thespian_Last_Name
From Thespian_Film_Table
Where Film_Title in ("Blade Runner", "Brazil", "The Matrix")
Order by 2;

Personally, I recommend specifying the field name instead of column number for clarity's sake, but this method is useful when one or more columns that you wish to order by is a derived or literal field instead of a base table value. For example:

Select TRIM(Film_Title) + " (" + Film_Year + ")", TRIM(Thespian_Last_Name) + ", " + Thespian_First_Name
From Thespian_Film_Table
Where Film_Title in ("Blade Runner", "Brazil", "The Matrix", "King Kong")
Order by 1, 2;

might have results of:

Blade Runner (1982) Ford, Harrison
Blade Runner (1982) Hannah, Daryl
Blade Runner (1982) Hauer, Rutger
Blade Runner (1982) Young, Sean
Brazil (1987) DeNiro, Robert
Brazil (1987) Palin, Michael
Brazil (1987) Pryce, Jonathan
King Kong (1933) Wray, Faye
King Kong (1976) Lange, Jessica
King Kong (2005) Watts, Naomi
The Matrix (1999) Reeves, Keanu

If instead it had specified Order by 2,1 the results are:

Brazil (1987) DeNiro, Robert
Blade Runner (1982) Ford, Harrison
Blade Runner (1982) Hannah, Daryl
Blade Runner (1982) Hauer, Rutger
King Kong (1976) Lange, Jessica
Brazil (1987) Palin, Michael
Brazil (1987) Pryce, Jonathan
The Matrix (1999) Reeves, Keanu
King Kong (2005) Watts, Naomi
King Kong (1933) Wray, Faye
Blade Runner (1982) Young, Sean

Comment by Larry Rate this Comment

Your information on the ORDER BY clause has a mistake. When using Microsoft SQL Server 7.0, the clause DESCEND causes an error. The correct "key word" as set forth by Microsoft is DESC.

I ran across this while trying to write some SQL code. Just thought I would let you know so that others can benefit.