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.
  • 1902

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     

Categories

More Articles

© 2018 DotNetHeaven. All rights reserved.