Alias by OrderBy Clause in T-SQL using 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