The ORDER BY clause is an optional element of a SELECT statement. When you execute SQL queries to retrieve data from a database server, you get a data set in whatever orders it is. Therefore you need SQL ORDER BY clause to sort the data in order you want it to be. An ORDER BY clause allows you to specify the order in which rows appear in the ResultSet.
SQL ORDER BY clause allows you to:
- Sort data set in single column of values of mutiple columns.
- Sort any column in either ascending order (ASC) by default or descending order(DESC)
- Refer to sort columns by name, by their position within the output column list of data set, or by using an alias.
It is permissible to combine ORDER BY on the outer query with ORDER BY in subqueries. It is not valid in views, inline functions, derived tables, and subqueries, unless TOP is also specified.
syntex:
ORDER BY { column-Name | ColumnPosition } [ ASC | DESC ] [ , column-Name |
ColumnPosition [ ASC | DESC ] ] *
Example:
Suppose we have two tables named Category and books look like:
Category_Id Category
--------------- -----------
1 Arts
2 science
3 commerce
4 phsical
books_ID books_Title publisher_ID Category_Id SalesPrice AverageCost
----------- ------------- -------------- -------------- ------------ --------------
001 ABCD 34561 2 200 180
002 HJFG 15465 2 225 200
003 YDJDF 14425 1 352 300
004 GJHNDF 65866 1 122 100
005 FGHFFF 86655 1 100 80
006 FNHDFFN 79256 3 90 70
007 HGNDGCF 49856 3 421 350
008 GFNSDG 45965 4 350 300
009 GDFTG 58546 1 100 90
010 GFBGB 56632 2 200 175
Here is the query:
SELECT books.Category_Id,Category.Category,
Count(books.books_ID) "Total sum"
FROM books,Category
WHERE books.Category_Id = Category.Category_Id
GROUP BY books.Category_Id,Category.Category
ORDER BY Category.Category
GO
Output:
Category_Id Category Total sum
-------------- ------------- -----------
3 commerce 2
2 science 3
4 phsical 1
1 Arts 4