/* ORDER BY example from http://idealprogrammer.com 
 
 PURPOSE: ORDER BY specifies how the rows in the result set should be sorted. The 
          order by expressions can be column names or a number representing
          the ordinal position of the column as it is listed in the select clause.
          The ORDER BY clause is not valid in views, inline functions, derived 
          tables, and subqueries, unless TOP is also specified.
 
 SYNTAX: 
 
 [ ORDER BY 
    {
    order_by_expression 
  [ COLLATE collation_name ] 
  [ ASC | DESC ] 
    } [ ,...n ] 
] 
 
 
 
 
 
 SELECT [ ALL | DISTINCT ] 
			[TOP ( expression ) [PERCENT] [ WITH TIES ] ] 
			 column_list  
			[ INTO new_table ] 
			[ FROM { table_source } [ ,...n ] ] 
			[ WHERE select_condition] 
			[ GROUP BY ] 
			[ HAVING  search_condition  ] 
			[ ORDER BY  order_expression [ ASC | DESC ] ] 
*/
 
 
CREATE TABLE people(
     ID          int,
     firstname   varchar (20),
     lastname    varchar (20),
     statecode   varchar (2),
     alive       bit,
     height      int )
 GO
 
 
 
 
 INSERT INTO people (ID, firstname, lastname, statecode, alive) --height is null 
 VALUES (1,  'Paul', 'Revere', 'AL', 0)
 GO
 
 INSERT INTO people (ID, firstname, lastname, statecode, alive, height) 
 VALUES (2,  'Pat', 'Lennon', 'NY', 0, 69)
 GO
 
 INSERT INTO people (ID, firstname, lastname, statecode, alive, height)
 VALUES (3,  'Peter', 'Martin', 'NY', 1, 75)
 GO
 
 INSERT INTO people (ID, firstname, lastname, statecode, alive, height)
 VALUES (4,  'George', 'Washington', 'VA', 0, 75)
 GO
 
 
 
 
 
 -- 1. Example of using a varchar column 
 --    The sort will be alphabetic and ascending
 
 SELECT 'Example #1' as Example, firstname, lastname, height 
  FROM people p
  ORDER BY firstname
 
 GO
 
 -- 2. Example of using an integer column and DESC
 --    Sort will be numeric and descending
 
SELECT 'Example #2' as Example, firstname, lastname, height 
  FROM people p
  ORDER BY height DESC
 
 GO
 
  -- 3. Example of using multiple columns and DESC
 --    Sort will be height ascending and then by first name descending
 
SELECT 'Example #3' as Example, firstname, lastname, height 
  FROM people p
  ORDER BY height, firstname DESC
 
 GO
 
  -- 4. Example of using ordinal position of column in select list
 --    Sort will be by first name 
 
 SELECT 'Example #4' as Example, firstname, lastname, height 
  FROM people p
  ORDER BY 2 -- firstname is the second column in select list
 
 GO
 
 DROP TABLE people;
 
 GO