SQL ORDER BY multiple columns [duplicate]

Question

I want to sort my products table by two columns: prod_price and prod_name.

SELECT prod_id, prod_price, prod_name FROM Products ORDER BY prod_price, prod_name; 

How is the sorting done here? I think it happens first by prod_price and then by prod_name. Also, how is the above query different from this one:

SELECT prod_id, prod_price, prod_name FROM Products ORDER BY prod_name; 

My products table is as follows:

CREATE TABLE Products (   prod_id    char(10)      NOT NULL ,   vend_id    char(10)      NOT NULL ,   prod_name  char(255)     NOT NULL ,   prod_price decimal(8,2)  NOT NULL ,   prod_desc  text          NULL  ); 

Answer 1

Sorting in an ORDER BY is done by the first column, and then by each additional column in the specified statement.

For instance, consider the following data:

Column1    Column2 =======    ======= 1          Smith 2          Jones 1          Anderson 3          Andrews 

The query

SELECT Column1, Column2 FROM thedata ORDER BY Column1, Column2 

would first sort by all of the values in Column1

and then sort the columns by Column2 to produce this:

Column1    Column2 =======    ======= 1          Anderson 1          Smith 2          Jones 3          Andrews 

In other words, the data is first sorted in Column1 order, and then each subset (Column1 rows that have 1 as their value) are sorted in order of the second column.

The difference between the two statements you posted is that the rows in the first one would be sorted first by prod_price (price order, from lowest to highest), and then by order of name (meaning that if two items have the same price, the one with the lower alpha value for name would be listed first), while the second would sort in name order only (meaning that prices would appear in order based on the prod_name without regard for price).

Answer 2

The results are ordered by the first column, then the second, and so on for as many columns as the ORDER BY clause includes. If you want any results sorted in descending order, your ORDER BY clause must use the DESC keyword directly after the name or the number of the relevant column.

Check out this Example

SELECT first_name, last_name, hire_date, salary  FROM employee  ORDER BY hire_date DESC,last_name ASC; 

It will order in succession. Order the Hire_Date first, then LAST_NAME it by Hire_Date .