Simple ORDER by : Sort Order « Select Clause « SQL / MySQL






  1. SQL / MySQL
  2. Select Clause
  3. Sort Order

Simple ORDER by

   
/*
mysql> select * from employee_person;
+----+-------------------------------------+---------+---------------------------+------------+------+----------+-----------------+----------+
| id | address                             | phone   | email | birthday   | sex  | m_status | s_name          | children |
+----+-------------------------------------+---------+---------------------------+------------+------+----------+-----------------+----------+
|  1 | 200, Regina Street                  | 7176666 | net@hotmail.com | 1971-04-26 | M    | Y        | Ane Regina      |     NULL |
|  2 | 1232 Alberta Road                   | 5553312 | jo@hotmail.com | 1968-03-02 | M    | Y        | Jane Van        |        3 |
|  3 | 90 Potter A                         | 3331211 | gp@ymail.com | 1967-09-22 | M    | N        | Sandhya Pil     |        2 |
|  4 | 202, Donna Street                   | 7176167 | twink@hotmail.com | 1976-08-09 | F    | Y        | Manish Sha      |     NULL |
|  5 | Apartment #8, UBC, Van Street       | 8973242 | holy@ymail.com | 1974-10-14 | F    | N        | NULL            |     NULL |
|  6 | 46 SFU Street                       | 6451234 | kill@el.com | 1978-12-31 | M    | N        | NULL            |     NULL |
|  7 | 432 Mercury Ave                     | 7932232 | mac@hotmail.com | 1966-08-21 | M    | Y        | Mary Shelly     |        3 |
|  8 | 8 Little YaleTown                   | 5442994 | edd@gmail.com | 1975-01-14 | M    | N        | NULL            |     NULL |
|  9 | 64 Temp Road                        | 4327652 | nan@pmail.com | 1969-05-19 | M    | Y        | Man Nanda       |        1 |
| 10 | 132 Metro House, Henry Street       | 5552376 | ra@hotmail.com | 1968-07-06 | M    | N        | NULL            |     NULL |
| 11 | 1 Grace Town, Van Avenue            | 5433879 | soundofsilence@boxer.net | 1957-11-04 | M    | Y        | Muriel Lovelace |        4 |
| 12 | 97 Oakland Road                     | 5423311 | kingarthur@roundtable.org | 1968-02-15 | M    | Y        | Rina Brighton   |        3 |
| 13 | 543 Applegate Lane                  | 3434343 | levy@cmail.com | 1968-09-03 | F    | Y        | Matt Shi        |        2 |
| 14 | 76 Fish Street                      | 7432433 | tink@email.com | 1965-04-28 | M    | N        | NULL            |     NULL |
| 15 | 98 Gun Street                       | 6500787 | danny@fhardy.com | 1966-06-23 | M    | Y        | Betty Cudly     |        3 |
| 16 | #5 Winnepag Homes                   | 5433243 | mike@cmail.com | 1964-03-06 | M    | Y        | Stella Stevens  |        2 |
| 17 | 652 Devon Building, 6th Jade Avenue | 5537885 | mona@darling.com | 1970-04-18 | F    | Y        | Edgar Alan      |        1 |
| 18 | Apartment #9, Together Towers       | 5476565 | odessey@hotmail.com | 1973-10-09 | M    | N        | NULL            |     NULL |
| 19 | Apartment #9, West Towers           | 5476565 | jire@hotmail.com | 1973-01-20 | M    | N        | NULL            |     NULL |
| 20 | 90 Yale Town                        | 7528326 | help@more.org | 1968-01-25 | F    | N        | NULL            |     NULL |
| 21 | 4329 Eucalyptus Avenue              | 4254863 | money@cold.com | 1964-06-13 | M    | Y        | Ruby Richer     |        2 |
+----+-------------------------------------+---------+---------------------------+------------+------+----------+-----------------+----------+
21 rows in set (0.00 sec)

mysql> select id, birthday
    -> from employee_person
    -> ORDER BY birthday;
+----+------------+
| id | birthday   |
+----+------------+
| 11 | 1957-11-04 |
| 16 | 1964-03-06 |
| 21 | 1964-06-13 |
| 14 | 1965-04-28 |
| 15 | 1966-06-23 |
|  7 | 1966-08-21 |
|  3 | 1967-09-22 |
| 20 | 1968-01-25 |
| 12 | 1968-02-15 |
|  2 | 1968-03-02 |
| 10 | 1968-07-06 |
| 13 | 1968-09-03 |
|  9 | 1969-05-19 |
| 17 | 1970-04-18 |
|  1 | 1971-04-26 |
| 19 | 1973-01-20 |
| 18 | 1973-10-09 |
|  5 | 1974-10-14 |
|  8 | 1975-01-14 |
|  4 | 1976-08-09 |
|  6 | 1978-12-31 |
+----+------------+
21 rows in set (0.00 sec)


*/       
Drop table employee_person;

CREATE TABLE employee_person (
    id int unsigned not null primary key, 
    address varchar(60), 
    phone int, 
    email varchar(60), 
    birthday DATE, 
    sex ENUM('M', 'F'), 
    m_status ENUM('Y','N'), 
    s_name varchar(40), 
    children int
);


INSERT INTO employee_person (id, address, phone, email, birthday, sex, m_status, s_name) values (1, '200, Regina Street', 7176666, 'net@hotmail.com', '1971-04-26', 'M', 'Y', 'Ane Regina');
INSERT INTO employee_person (id, address, phone, email, birthday, sex, m_status, s_name, children) values (2, '1232 Alberta Road', 5553312, 'jo@hotmail.com', '1968-03-02', 'M', 'Y', 'Jane Van', 3);
INSERT INTO employee_person (id, address, phone, email, birthday, sex, m_status, s_name, children) values (3, '90 Potter A', 3331211, 'gp@ymail.com', '1967-09-22', 'M', 'N', 'Sandhya Pil', 2);
INSERT INTO employee_person (id, address, phone, email, birthday, sex, m_status, s_name) values (4, '202, Donna Street', 7176167, 'twink@hotmail.com', '1976-08-09', 'F', 'Y', 'Manish Sha');
INSERT INTO employee_person (id, address, phone, email, birthday, sex, m_status) values (5, 'Apartment #8, UBC, Van Street', 8973242, 'holy@ymail.com', '1974-10-14', 'F', 'N');
INSERT INTO employee_person (id, address, phone, email, birthday, sex, m_status) values (6, '46 SFU Street', '6451234', 'kill@el.com', '1978-12-31', 'M', 'N');
INSERT INTO employee_person (id, address, phone, email, birthday, sex, m_status, s_name, children) values (7, '432 Mercury Ave', 7932232, 'mac@hotmail.com', '1966-8-21', 'M', 'Y', 'Mary Shelly', '3');
INSERT INTO employee_person (id, address, phone, email, birthday, sex, m_status) values (8, '8 Little YaleTown', 5442994, 'edd@gmail.com', '1975-01-14', 'M', 'N');
INSERT INTO employee_person (id, address, phone, email, birthday, sex, m_status, s_name, children) values (9, '64 Temp Road', 4327652, 'nan@pmail.com', '1969-05-19', 'M', 'Y', 'Man Nanda', '1');
INSERT INTO employee_person (id, address, phone, email, birthday, sex, m_status) values (10, '132 Metro House, Henry Street', 5552376, 'ra@hotmail.com', '1968-07-06', 'M', 'N');
INSERT INTO employee_person (id, address, phone, email, birthday, sex, m_status, s_name, children) values (11, '1 Grace Town, Van Avenue', 5433879, 'soundofsilence@boxer.net', '1957-11-04', 'M', 'Y', 'Muriel Lovelace', '4');
INSERT INTO employee_person (id, address, phone, email, birthday, sex, m_status, s_name, children) values (12, '97 Oakland Road', 5423311, 'kingarthur@roundtable.org', '1968-02-15', 'M', 'Y', 'Rina Brighton', 3);
INSERT INTO employee_person (id, address, phone, email, birthday, sex, m_status, s_name, children) values (13, '543 Applegate Lane', 3434343, 'levy@cmail.com', '1968-09-03', 'F', 'Y', 'Matt Shi', '2');
INSERT INTO employee_person (id, address, phone, email, birthday, sex, m_status) values (14, '76 Fish Street', 7432433, 'tink@email.com', '1965-04-28', 'M', 'N');
INSERT INTO employee_person (id, address, phone, email, birthday, sex, m_status, s_name, children) values (15, '98 Gun Street', 6500787, 'danny@fhardy.com', '1966-06-23', 'M', 'Y', 'Betty Cudly', 3);
INSERT INTO employee_person (id, address, phone, email, birthday, sex, m_status, s_name, children) values (16, '#5 Winnepag Homes', 5433243, 'mike@cmail.com', '1964-03-06', 'M', 'Y', 'Stella Stevens', 2);
INSERT INTO employee_person (id, address, phone, email, birthday, sex, m_status, s_name, children) values (17, '652 Devon Building, 6th Jade Avenue', 5537885, 'mona@darling.com', '1970-04-18', 'F', 'Y', 'Edgar Alan', 1);
INSERT INTO employee_person (id, address, phone, email, birthday, sex, m_status) values (18, 'Apartment #9, Together Towers', 5476565, 'odessey@hotmail.com', '1973-10-09', 'M', 'N');
INSERT INTO employee_person (id, address, phone, email, birthday, sex, m_status) values (19, 'Apartment #9, West Towers', 5476565, 'jire@hotmail.com', '1973-1-20', 'M', 'N');
INSERT INTO employee_person (id, address, phone, email, birthday, sex, m_status) values (20, '90 Yale Town', 7528326, 'help@more.org', '1968-01-25', 'F', 'N');
INSERT INTO employee_person (id, address, phone, email, birthday, sex, m_status, s_name, children) values (21, '4329 Eucalyptus Avenue', 4254863, 'money@cold.com', '1964-06-13', 'M', 'Y', 'Ruby Richer', 2);

select * from employee_person;

select id, birthday
from employee_person
ORDER BY birthday;








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.Sorting Data
16.Use order by to sort the result
17.Indicate of ascend
18.Order by index
19.ORDER BY RAND
20.Search string with order
21.The lack of case sensitivity also applies to relative ordering comparisons:
22.Refer to the alias in the ORDER BY clause
23.Columns specified by positions or by aliases can be sorted in either ascending or descending order
24.Putting the expression directly in the ORDER BY clause:
25.To achieve the desired output order, display the string, but use the actual numeric size for sorting
26.Display the composite names, but refer to the constituent values in the ORDER BY clause:
27.To sort those records in calendar order, use the birthmonth and birthday columns.
28.To sort by product category, extract the category value and use it in the ORDER BY clause
29.To use the substrings for sorting, use the appropriate expressions in the ORDER BY clause.
30.Sorting Hostnames in Domain Order
31.Sorting Dotted-Quad IP Values in Numeric Order
32.Floating Specific Values to the Head or Tail of the Sort Order
33.Sorting in User-Defined Orders
34.Sort the column by the order in which colors occur in the rainbow.
35.To make the lexical ordering correspond to the numeric ordering,
36.Controlling Summary Display Order
37.Sort drivers according to who drove the most days or miles, add the appropriate ORDER BY clause
38.Order by sum result
39.To sort the result set as a whole, add an ORDER BY clause after the final SELECT statement.
40.Enclose a given SELECT (including its ORDER BY clause) within parentheses
41.The expressions display state names in lexical order within each row
42.Delete from the Orders table any order for the book title Where I'm Calling From.
43.SELECT statement includes an ORDER BY clause that sorts the result set according to two columns
44.Determine how many books have been ordered for authors who have more than one book listed in the Books table.
45.The Order in Which MySQL Processes Conditions
46.Return the third, fourth, and fifth records sorted in descending order on the commission field?
47.Explicit evaluation order
48.Display order number, quantity, item name, vendor and total order value of order number 2805
49.Display all products - including those with no orders
50.Get the order number and number of items ordered where the color is not Pink and the number of items ordered i
51.Get players whose combination of name and initials comes before player 6 in alphabetical order.
52.Order by char type
53.order by sub string
54.Order by calculated value
55.Order by alias name
56.Order by one column descending and another one ascending
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