ORDER BY – SQL Server Syntax Example: ORDER BY – T-SQL Example

Get 5 Hours of FREE PREMIUM Videos:

LearnVisualStudio.NET Free Preview

LearnVisualStudio Premium Videos

dotNetVideos Premium Video Catalog

ORDER BY – SQL Server Syntax Example: ORDER BY – T-SQL Example

Purpose: – Illustrates the SQL Server syntax for ORDER BY.

SYNTAX:
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_by_expression [ COLLATE collation_name ] [ ASC | DESC ]} [ ,...n ] ]

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.

Code Sample for ORDER BY:

 T-SQL | 
 copy code |
?
 
 
/* 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
 
 
 
 

Sample Output for ORDER BY Example

Related posts:

1. DESC – SQL Server Syntax Example: DESC – T-SQL Example DESC - SQL Server Syntax Example: DESC - T-SQL Example...
2. HAVING – SQL Server Syntax Example: HAVING – T-SQL Example HAVING - SQL Server Syntax Example: HAVING - T-SQL Example...
3. ISNULL – SQL Server Syntax Example: ISNULL – T-SQL Example ISNULL - SQL Server Syntax Example: ISNULL - T-SQL Example...
4. DISTINCT – SQL Server Syntax Example: DISTINCT – T-SQL Example DISTINCT - SQL Server Syntax Example: DISTINCT - T-SQL Example...
5. NULL – SQL Server Syntax Example: NULL – T-SQL Example NULL - SQL Server Syntax Example: NULL - T-SQL Example...

Related posts brought to you by Yet Another Related Posts Plugin.