Post
Ask Question

SQL SELECT ORDER BY Statement

Introduction  

In this article, we will learn about SQL Select Order By statement. This statement is used to sort the result set of a Select query in a specific order. It allows you to sort the data in ascending or descending Order based on one or more columns.

This syntax is using ORDER BY with a select statement as follows. Use the Desc keyword.

Syntax 

SELECT column1, column2, ...
FROM table_name
ORDER BY column1 [ASC|DESC], column2 [ASC|DESC], ...;

In the above syntax, column1, column2, and so on are the names of the columns you want to select from the table_name. You can select one or more columns in the Select statement.

The ORDER BY clause is used to sort the result set based on one or more columns. You can specify the sorting order for each column by using the keywords ASC (ascending) or DESC (descending).

 Select Order By statement Sorts data returned by a query in SQL Server. Use this clause too.

  • Order the result set of a query by the specified column list and limit the rows returned to a specified range optionally. The Order in which rows are returned in a result set is not guaranteed unless an ORDER BY clause is specified.
  •  Determine the Order in which ranking function values are applied to the result set.  

Statement 1. Using specifying a single column defined in the select list

In a SQL Select statement, you can specify a single column defined in the select list to use in the ORDER BY clause. This allows you to sort the result set by a specific column.

SELECT OrderName, orderAddress, OrderDate 
FROM OrderDetail
WHERE OrderName LIKE 'Tea%'
ORDER BY OrderId;

Note. that the 'Tea%' pattern in the WHERE clause uses the percent sign (%) as a wildcard to match any OrderName value that starts with 'Tea.' The ORDER BY clause sorts the results in ascending Order by the OrderId column. It's worth noting that this query assumes that the OrderDetail table has columns named OrderName, orderAddress, OrderDate, and OrderId. If these column names are different, the query would need to be adjusted accordingly.

Output 

Statement 2. Using Specify a column that is not defined in the select list

When you specify a column defined in the Select list, you will typically receive an error message indicating that the column is invalid or not found. This is because the column you are trying to reference is not included in the list of columns that are being selected.

If you need to include additional columns in your query, you should modify the Select statement to include those columns in the list of columns that are being selected. For example, you could add the following missing column to the Select statement.

SELECT OrderName, orderAddress, OrderDate      
FROM OrderDetails      
ORDER BY OrderName;

The above SQL statement will select the columns OrderName, orderAddress, and OrderDate from the table OrderDetails, and then sort the result set in ascending Order by the OrderName column. The resulting output will include all rows in the OrderDetails table, but only the selected columns will be displayed. Assuming that the OrderDetails table contains data about orders placed by customers, the query will return the names of the customers who placed the orders, their addresses, and the dates on which the orders were placed. The output will be sorted alphabetically by the names of the customers who placed the orders.

Output  

Statement 3. Using Specifying an alias as the sort column 

In this statement, you can use an alias to sort the result set by a column that is not directly selected in the Select clause. However, the alias must be specified in the ORDER BY clause, not the Select clause. 

SELECT column1 + column2 AS sum
FROM mytable
ORDER BY sum DESC;

 In this example, we select the sum of  column1 and column2 and give it an alias of the sum. We then sort the result set by the sum column in descending Order.

Note. that not all database systems allow aliases to beOrder By clauses. In those cases, you must repeat the expression to generate the alias in the ORDER BY clause. 

SELECT OrderName + orderAddress AS OrderAddressDetails
FROM OrderDetail
ORDER BY OrderName + orderAddress DESC;

This SQL query selects the concatenated value of OrderName and orderAddress columns from the OrderDetail table and labels it as "OrderAddressD." ails." It then sorts the results in descending Order based on the concatenated values of OrderName and orderAddress.

In other words, it will return a list of OrderAddressDetails, where each combination of the OrderName and orderAddress values for a specific order in the OrderDetail table. The list will be sorted in descending Order based on the concatenated values of OrderName and orderAddress.

It's important to note that the syntax and functionality of this query may vary depending on the specific database management system being used.  

Output 

Statement 4. Using Specifying an expression as the sort column

The following example uses an expression as the sort column. The expression is a deed using the DatePart function to sort the result set by the year in which OrderDetails were hired. 

SELECT OrderId, OrderName, orderAddress
FROM OrderDetails
ORDER BY DATEPART(year, OrderId);

This query will return the "OrderId," "OrderName," and "orderAddress" columns from the "OrderDetails" table, sorted by the year of the "OrderId" column. The "DATEPART" function is the year component from the "OrderId" column.

The fourth line specifies that the results should be ordered by the year of the OrderDate. Specifically, the DATEPART function extracts the year from the OrderDate column, and the results are ranked in ascending Order based on the year.

Output

Statement 5. Using Specifying a descending order  

When using the "ORDER BY" clause in a SQL query, you can specify the Order in which the results are returned by adding the "ASC" or "DESC" keyword after the column name. To specify a descending order, use the "DESC" keyword after the column name in the "ORDER BY" clause.

The following example orders the result set by the numeric column OrderId in descending Order.

SELECT OrderName, orderAddress  FROM OrderDetails      
WHERE OrderName LIKE 'Tea%'      
ORDER BY OrderId DESC;        

The given SQL query will select the "OrderName" and "orderAddress" columns from the "OrderDetails" table and filter the results only to include rows where the "OrderName" starts with "Tea" (using the "LIKE" operator with the wildcard symbol "%"), and then sort the filtered results in descending Order based on the "OrderId" column.

The resulting output will include the "OrderName" and "orderAddress" columns for all rows in the "OrderDetails" table where the "OrderName" starts with "Tea," sorted in descending Order based on the "OrderId" column. Here's an example of what the output might look like

Output  

Statement 6. Using specifying an ascending order

To specify an ascending order, you can use the keyword "ASC" (short for "ascending") in your query. This will tell the database to sort the ascending order results based on the specified column or columns.

The following example orders the result set by OrderName, the column in ascending Order.

SELECT OrderName, OrderAddress 
FROM OrderDetails 
WHERE OrderName LIKE 'Apple%' 
ORDER BY OrderName ASC;

This query would return all rows from the OrderDetails table where the OrderName starts with 'Apple,' sorted by the OrderName in ascending Order. The % symbol is a wildcard that matches any number of characters, so in this case, it matches any OrderName that starts with 'Apple.'

Output 

Statement 7. Using specifying both ascending and descending Order 

SQL query that selects the order name order address from the table "OrderDetails." This query orders the result set by two columns. The query result set is first sorted in ascending Order by the OrderName column and then sorted in descending Order by the OrderAddress column. The results are then ordered in ascending Order by order name and in descending Order by order address. Assuming the table "OrderDetails" exists in the database, this query should return a result set that includes all order names and addresses where the order name starts with the  ' 'P.' The results will be sorted first by order name in ascending Order and then by order address in descending Order. 

Output 

Statement 8. Using Specifying a collation In OrderBy statements

 In this example, Collation is the rule defining how data is sorted and compared in a database. By specifying a collation in an ORDER BY statement, you can control the Order in which the data is sorted based on a particular language, culture, or encoding. In most databases, you can specify the Collation in the ORDER BY statement by using the COLLATE keyword, followed by the name of the Collation. For example, if you want to sort data case-insensitively, you can use the following query in SQL Server. In the second query, a case-sensitive, accent-sensitive collation is specified in the OrderBy clause, which changes the Order in which the rows are returned. ​​​​​

SELECT OrderName
FROM OrderDetails
ORDER BY OrderName COLLATE SQL_Latin1_General_CP1_CI_AS

This SQL query selects the column "OrderName" from the table "OrderDetails" and orders the results based on the "OrderName" column using a specific collation sequence, "SQL_Latin1_General_CP1_CI_AS". The "ORDER BY" clause is used to sort the results in ascending or descending order based on one or more columns. In this case, the "OrderName" column is used for sorting. The "COLLATE" keyword specifies the collation sequence for sorting. Collation determines the rules for comparing and sorting characters in a character set. In this query, "SQL_Latin1_General_CP1_CI_AS" is the collation sequence ch is case-insensitive and accent-sensitive for Latin characters. 

Output  

Statement 9. Using Order by specifying a conditional order

The following examples use the Case expression in an ORDER BY clause to conditionally determine the sort order of the rows based on a given column value. The value in the OrderName column of the OrderDetails table is evaluated in the first example. Employees with the OrderName set to 1 are returned in Order by the OrderId in descending Order. Employees with the OrderName set to 0 are returned in Order by the OrderId in ascending Order.

In the second example, the result set is ordered by the column OrderName. 

SELECT OrderName, OrderAddress    
FROM OrderDetails
ORDER BY OrderName DESC, 
         CASE WHEN OrderName  = (SELECT MAX(OrderName) FROM OrderDetails) 
              THEN OrderName
              ELSE '' 
         END ASC;  

This SQL statement selects the "OrderName" and "OrderAddress" columns from the "OrderDetails" table and then orders the results based on two criteria.

  • The "OrderName" column is in descending Order.
  • The "OrderName" column is again in ascending Order, but only for the row with the maximum value of "Ord."Name".

The second ordering criteria is achieved using a Case statement, which evaluates whether the current row's "OrderName" equals the maximum "OrderName" value in the table. If it is, then the value of "OrderName" is used to sort the row in ascending Order. Otherwise, an empty string is used to sort the row. 

Output 

Statement 10. Using Order by a statement in a ranking function

The following example uses the ORDER BY clause in the ranking functions ROW_NUMBER, RANK, DENSE_RANK, and NTILE. 

SELECT OrderId, OrderName, OrderAddress,
ROW_NUMBER() OVER
(PARTITION BY OrderName ORDER BY OrderId DESC) as rank
FROM OrderDetails 
ORDER BY orderName, rank;

The SQL query selects the columns "OrderId," "OrderName," and "OrderAddress" from the "OrderDetails" table. It adds a new calculated column "rank" using the "ROW_NUMBER()" function with "PARTITION BY OrderName" and "ORDER BY OrderId DESC" clauses.

The "PARTITION BY" clause divides the result set into partitions based on the distinct values of "OrderName," and the "ORDER BY" clause specifies the Order of rows within each partition based on descending Order of "OrderId."

The "rank" column assigned the rank of each row within its respective partition based on the Order specified by the "ORDER BY" clause.

Finally, the result set is ordered by "OrderName" and "rank" in ascending Order.

Note that the actual output of this query would depend on the specific data in the "OrderDetails" table. 

Output 

Conclusion 

You learned how to use a SQL SELECT ORDER BY  statement with various options in this article. 

FAQs 

Q- What does the ORDER BY clause do in SQL?

A- The ORDER BY clause is used to sort the result set of a query by one or more columns in ascending or descending Order.

Q- Can I use multiple columns in the ORDER BY clause?

A- Yes, you can specify multiple columns in the ORDER BY clause, separated by commas. The query will first have the result set by the first column and then by the second column if there are ties.

Q- Can I use a column alias in the ORDER BY clause?

A- Yes, you can use a column alias in the ORDER BY clause, as long as the alias is defined in the SELECT clause before the ORDER BY clause.

Q- Can I use a calculated expression in the ORDER BY clause?

A- Yes, you can use a calculated expression in the ORDER BY clauses, such as a mathematical operation, a function, or a concatenation of columns.

Q- Can I specify different sorting orders for columns in the ORDER BY clause?

A- Yes, you can specify the sorting order (ascending or descending) for each column in the ORDER BY clause using the keywords ASC or DESC, respectively.

Q- Can I use the ORDER BY clause with a grouping or ranking function?

A- Yes, you can use the ORDER BY clause with grouping functions like GROUP BY or ranking functions like ROW_NUMBER(), RANK(), or DENSE_RANK() to determine the Order in which the groups or ranks are assigned.


Similar Articles