Using alias by OrderBy Clause in T-SQL in VB.NET
A sort column can be specified as a name or column alias, learn in this article how we used this with OrderBy clause.
As you learned about OrderBy Clause
in my
previous article, 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.
Alias: A
sort column can be specified as a name or column alias, or a nonnegative integer
representing the position of the name or alias in the select list. Select-list
items fetched from a cursor associated with ROWTYPE must have simple names or,
if they are expressions, must have aliases. An integer cannot be specified when
the order_by_expression appears in a ranking function.
"It is generally advisable to
create tuple variables (aliases) for all the tables referenced in an SQL query
to avoid potential errors and ambiguities"
Example:
Suppose we have a tables named books look like:
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
Here is the query:
SELECT books_ID,
books_Title + ',' + publisher_ID +'' + Category_Id As details
FROM books
ORDER BY books_Title
GO
Output:
books_ID details
----------- ------------------------
006 FNHDFFN, 79256 2
005 FGHFFF, 86655 2
004 GJHNDF, 65866 1
003 YDJDF, 14425 1
002 HJFG, 15465 1
001 ABCD, 34561 3