[mysql] SQL ORDER BY multiple columns

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 
);

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?

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.

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 .

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..