SQL multiple column ordering

I am trying to sort by multiple columns in SQL, and in different directions. column1 would be sorted descending, and column2 ascending.

How can I do this?

sqlsql-order-by

Answers

answered 9 years ago Ignacio Vazquez-Abrams #1

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.

answered 9 years ago Quassnoi #2

SELECT  *
FROM    mytable
ORDER BY
        column1 DESC, column2 ASC

answered 5 years ago Thomas C. G. de Vilhena #3

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

answered 2 years ago Jason Clark #4

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.

enter image description here

Now I perform Order By in these two columns by executing below command:

enter image description here

Now again I insert new values in these two columns, where Alphabet value in ASC order:

enter image description here

and the columns in Example table look like this. Now again perform the same operation:

enter image description here

You can see the values in the first column are in desc order but second column is not in ASC order.

answered 2 years ago atik sarker #5

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 

comments powered by Disqus