Order by one column descending and another one ascending

       
mysql> CREATE TABLE EmployeeS(
    ->          EmployeeNO       INTEGER      NOT NULL,
    ->          NAME           CHAR(15)     NOT NULL,
    ->          INITIALS       CHAR(3)      NOT NULL,
    ->          BIRTH_DATE     DATE                 ,
    ->          SEX            CHAR(1)      NOT NULL,
    ->          JOINED         SMALLINT     NOT NULL,
    ->          STREET         VARCHAR(30)  NOT NULL,
    ->          HOUSENO        CHAR(4)              ,
    ->          POSTCODE       CHAR(6)              ,
    ->          TOWN           VARCHAR(30)  NOT NULL,
    ->          PHONENO        CHAR(13)             ,
    ->          LEAGUENO       CHAR(4)              ,
    ->          PRIMARY KEY    (EmployeeNO)           );
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO EmployeeS VALUES (2, 'Jack', 'R', '1948-09-01', 'M', 1975, 'Stoney Road','43', '3575NH', 'Stratford', '070-237893', '2411');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO EmployeeS VALUES (6, 'Link', 'R', '1964-06-25', 'M', 1977, 'Haseltine Lane','80', '1234KK', 'Stratford', '070-476537', '8467');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO EmployeeS VALUES (7, 'Wise', 'GWS', '1963-05-11', 'M', 1981, 'First Way','39', '9758VB', 'Stratford', '070-347689', NULL);
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO EmployeeS VALUES (8, 'Mary', 'B', '1962-07-08', 'F', 1980, 'Station Road','4', '6584WO', 'Inglewood', '070-458458', '2983');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO EmployeeS VALUES (27, 'Collins', 'DD', '1964-12-28', 'F', 1983, 'Long DRay','804', '8457DK', 'Eltham', '079-234857', '2513');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO EmployeeS VALUES (28, 'Collins', 'C', '1963-06-22', 'F', 1983, 'Old Main Road','10', '1294QK', 'Midhurst', '010-659599', NULL);
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO EmployeeS VALUES (39, 'Bishop', 'D', '1956-10-29', 'M', 1980, 'Eaton Square','78', '9629CD', 'Stratford', '070-393435', NULL);
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO EmployeeS VALUES (44, 'Baker', 'E', '1963-01-09', 'M', 1980, 'Lewis Street','23', '4444LJ', 'Inglewood', '070-368753', '1124');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO EmployeeS VALUES (57, 'Brown', 'M', '1971-08-17', 'M', 1985, 'First Way','16', '4377CB', 'Stratford', '070-473458', '6409');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO EmployeeS VALUES (83, 'Hope', 'PK', '1956-11-11', 'M', 1982, 'Main Road','16A', '1812UP', 'Stratford', '070-353548', '1608');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO EmployeeS VALUES (95, 'Miller', 'P', '1963-05-14', 'M', 1972, 'High Street','33A', '5746OP', 'Douglas', '070-867564', NULL);
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO EmployeeS VALUES (100, 'Link', 'P', '1963-02-28', 'M', 1979, 'Haseltine Lane','80', '6494SG', 'Stratford', '070-494593', '6524');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO EmployeeS VALUES (104, 'Jane', 'D', '1970-05-10', 'F', 1984, 'Stout Street','65', '9437AO', 'Eltham', '079-987571', '7060');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO EmployeeS VALUES (112, 'Bailey', 'IP', '1963-10-01', 'F', 1984, 'Vixen Road','8', '6392LK', 'Plymouth', '010-548745', '1319');
Query OK, 1 row affected (0.00 sec)

mysql>
mysql>
mysql> CREATE   TABLE PENALTIES
    ->         (PAYMENTNO      INTEGER      NOT NULL,
    ->          EmployeeNO       INTEGER      NOT NULL,
    ->          PAYMENT_DATE   DATE         NOT NULL,
    ->          AMOUNT         DECIMAL(7,2) NOT NULL,
    ->          PRIMARY KEY    (PAYMENTNO)          );
Query OK, 0 rows affected (0.00 sec)

mysql>
mysql> INSERT INTO PENALTIES VALUES (1,  6, '1980-12-08',100);
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO PENALTIES VALUES (2, 44, '1981-05-05', 75);
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO PENALTIES VALUES (3, 27, '1983-09-10',100);
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO PENALTIES VALUES (4,104, '1984-12-08', 50);
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO PENALTIES VALUES (5, 44, '1980-12-08', 25);
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO PENALTIES VALUES (6,  8, '1980-12-08', 25);
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO PENALTIES VALUES (7, 44, '1982-12-30', 30);
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO PENALTIES VALUES (8, 27, '1984-11-12', 75);
Query OK, 1 row affected (0.00 sec)

mysql>
mysql> SELECT   EmployeeNO, AMOUNT
    -> FROM     PENALTIES
    -> ORDER BY EmployeeNO DESC, AMOUNT ASC;
+------------+--------+
| EmployeeNO | AMOUNT |
+------------+--------+
|        104 |  50.00 |
|         44 |  25.00 |
|         44 |  30.00 |
|         44 |  75.00 |
|         27 |  75.00 |
|         27 | 100.00 |
|          8 |  25.00 |
|          6 | 100.00 |
+------------+--------+
8 rows in set (0.00 sec)

mysql>
mysql> drop table Employees;
Query OK, 0 rows affected (0.00 sec)

mysql> drop table penalties;
Query OK, 0 rows affected (0.00 sec)

Related examples in the same category

1.	Order result wiht ORDER
2.	Use ORDER BY to list
3.	Use two ORDER BY fields
4.	Sorting Rows
5.	Default sort order is ascending
6.	To sort in reverse (descending) order
7.	Sort on multiple columns
8.	Sort columns in different directions
9.	Order decending
10.	Another decendingly
11.	Order BY and Limit
12.	Order row in select clause
13.	Order two columns with different orders
14.	Narrow down data with condition and order it
15.	Simple ORDER by
16.	Sorting Data
17.	Use order by to sort the result
18.	Indicate of ascend
19.	Order by index
20.	ORDER BY RAND
21.	Search string with order
22.	The lack of case sensitivity also applies to relative ordering comparisons:
23.	Refer to the alias in the ORDER BY clause
24.	Columns specified by positions or by aliases can be sorted in either ascending or descending order
25.	Putting the expression directly in the ORDER BY clause:
26.	To achieve the desired output order, display the string, but use the actual numeric size for sorting
27.	Display the composite names, but refer to the constituent values in the ORDER BY clause:
28.	To sort those records in calendar order, use the birthmonth and birthday columns.
29.	To sort by product category, extract the category value and use it in the ORDER BY clause
30.	To use the substrings for sorting, use the appropriate expressions in the ORDER BY clause.
31.	Sorting Hostnames in Domain Order
32.	Sorting Dotted-Quad IP Values in Numeric Order
33.	Floating Specific Values to the Head or Tail of the Sort Order
34.	Sorting in User-Defined Orders
35.	Sort the column by the order in which colors occur in the rainbow.
36.	To make the lexical ordering correspond to the numeric ordering,
37.	Controlling Summary Display Order
38.	Sort drivers according to who drove the most days or miles, add the appropriate ORDER BY clause
39.	Order by sum result
40.	To sort the result set as a whole, add an ORDER BY clause after the final SELECT statement.
41.	Enclose a given SELECT (including its ORDER BY clause) within parentheses
42.	The expressions display state names in lexical order within each row
43.	Delete from the Orders table any order for the book title Where I'm Calling From.
44.	SELECT statement includes an ORDER BY clause that sorts the result set according to two columns
45.	Determine how many books have been ordered for authors who have more than one book listed in the Books table.
46.	The Order in Which MySQL Processes Conditions
47.	Return the third, fourth, and fifth records sorted in descending order on the commission field?
48.	Explicit evaluation order
49.	Display order number, quantity, item name, vendor and total order value of order number 2805
50.	Display all products - including those with no orders
51.	Get the order number and number of items ordered where the color is not Pink and the number of items ordered i
52.	Get players whose combination of name and initials comes before player 6 in alphabetical order.
53.	Order by char type
54.	order by sub string
55.	Order by calculated value
56.	Order by alias name
57.	Order then choose the first rows
58.	Refer to Sort Columns
59.	Sorting Expression Results
60.	Sort the results using the underlying column values rather than the displayed composite values
61.	Convert the output column and sort that-but doing so affects the displayed values, possibly in an undesirable
62.	To sort by country code, use the rightmost two characters of the id values
63.	Sorting hostname values correctly in right-to-left fashion:
64.	Sort by position
65.	Sort direction
66.	display all data in "hers" and "his" sorted by id
67.	Sorting Subsets of a Table
68.	Displaying One Set of Values While Sorting by Another

Order by one column descending and another one ascending : Sort Order « Select Clause « SQL / MySQL

Related examples in the same category