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

score 37 · Accepted Answer · answered Mar 26 '13 at 02:48

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

And just for fun (but not only for fun): there is another way to sort data in DBMS which supports window syntax: `select *, count(*) over (order by Column1, Column2) from thedata` — Abelisto, Mar 26 '13 at 05:00

score 8 · Answer 2 · edited Feb 15 '17 at 09:46

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 .

score 1 · Answer 3 · answered Mar 26 '13 at 02:29

1

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?

answered Mar 26 '13 at 02:29

Erwin Brandstetter

605,456
145
1,078
1,228

I think sorting by prod_name only gets done when prod_price columns have same values otherwise it gets sorted by prod_name . Please tell if i am correct – user2201462 Mar 26 '13 at 02:41
@user2201462: That's what the word *peer* means. Those rows are all the same in regard to a certain `ORDER BY` item. Only those are subject to the next item. – Erwin Brandstetter Mar 26 '13 at 02:43

score 1 · Answer 4 · edited Oct 23 '17 at 18:31

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.

score 1 · Answer 5 · answered Feb 19 '14 at 03:28

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

SQL ORDER BY multiple columns

5 Answers5

Linked

Related