SQL multiple column ordering
SqlSql Order-BySql Problem Overview
How can I sort by multiple columns in SQL and in different directions. column1
would be sorted descending, and column2
ascending.
Sql Solutions
Solution 1 - Sql
ORDER BY column1 DESC, column2
This sorts everything by column1
(descending) first, and then by column2
(ascending, which is the default) whenever the column1
fields for two or more rows are equal.
Solution 2 - Sql
The other answers lack a concrete example, so here it goes:
Given the following People table:
FirstName | LastName | YearOfBirth
----------------------------------------
Thomas | Alva Edison | 1847
Benjamin | Franklin | 1706
Thomas | More | 1478
Thomas | Jefferson | 1826
If you execute the query below:
SELECT * FROM People ORDER BY FirstName DESC, YearOfBirth ASC
The result set will look like this:
FirstName | LastName | YearOfBirth
----------------------------------------
Thomas | More | 1478
Thomas | Jefferson | 1826
Thomas | Alva Edison | 1847
Benjamin | Franklin | 1706
Solution 3 - Sql
SELECT *
FROM mytable
ORDER BY
column1 DESC, column2 ASC
Solution 4 - Sql
Multiple column ordering depends on both column's corresponding values: Here is my table example where are two columns named with Alphabets and Numbers and the values in these two columns are asc and desc orders.
Now I perform Order By in these two columns by executing below command:
Now again I insert new values in these two columns, where Alphabet value in ASC order:
and the columns in Example table look like this. Now again perform the same operation:
You can see the values in the first column are in desc order but second column is not in ASC order.
Solution 5 - Sql
You can use multiple ordering on multiple condition,
ORDER BY
(CASE
WHEN @AlphabetBy = 2 THEN [Drug Name]
END) ASC,
CASE
WHEN @TopBy = 1 THEN [Rx Count]
WHEN @TopBy = 2 THEN [Cost]
WHEN @TopBy = 3 THEN [Revenue]
END DESC
Solution 6 - Sql
SELECT id,
first_name,
last_name,
salary
FROM employee
ORDER BY salary DESC, last_name;
If you want to select records from a table but would like to see them sorted according to two columns, you can do so with ORDER BY. This clause comes at the end of your SQL query.
After the ORDER BY keyword, add the name of the column by which you’d like to sort records first (in our example, salary). Then, after a comma, add the second column (in our example, last_name). You can modify the sorting order (ascending or descending) separately for each column. If you want to use ascending (low to high) order, you can use the ASC keyword; this keyword is optional, though, as that is the default order when none is specified. If you want to use descending order, put the DESC keyword after the appropriate column (in the example, we used descending order for the salary column).
Solution 7 - Sql
You can also sort or order by the Number of Characters in each Column you wish to sort by. Shown below is a sample which sorts by the first three characters of the First Name and by the last two characters in the name of the town.
SELECT *
FROM table_name
ORDER BY LEFT(FirstName, 3) ASC, LEFT(Town, 2);