SQL ORDER BY multiple columns

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

User · Answer

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 .

User · Answer

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

User · Answer

yes the  sorting  proceed differently   in first scenario  orders based on column1  and  in addition to that process further by sorting colmun2 based on column1    in second scenario   it orders completely based on column 1 only    please  proceed  with a  simple example   u  will get  quickly

User · Answer

It depends on the size of your database    SQL is based on the SET theory  there is no order inherently used when querying a table   So if you were to run the first query  it would first order by product price and then product name  IF there were any duplicates in the price category  say  20 for example  it would then order those duplicates by their names  therefore always maintaining that when you run your query it will always return the same set of result in the same order   If you were to run the second query  it would only order by the name  so if there were two products with the same name  for some odd reason  then they wouldn t have a guaranteed order after you run the query

User · Answer

Yes  the sorting is different    Items in the ORDER BY list are applied in order  Later items only order peers left from the preceding step   Why don t you just try

[mysql] SQL ORDER BY multiple columns

Examples related to mysql

Examples related to sql

Examples related to sql-order-by