SQL/MySQL/Select Clause/Sort Order

Содержание

1 Another decendingly
2 Default sort order is ascending
3 Indicate of ascend
4 Narrow down data with condition and order it
5 Order BY and Limit
6 Order decending
7 Order result wiht ORDER
8 Order row in select clause
9 Order two columns with different orders
10 Simple ORDER by
11 Sort columns in different directions
12 Sorting Data
13 Sorting Rows
14 Sort on multiple columns
15 To sort in reverse (descending) order
16 Use ORDER BY to list
17 Use order by to sort the result
18 Use two ORDER BY fields

Another decendingly

   <source lang="sql">

/*mysql> select * from employee; +----+-----------+----------+----------------------------+------+---------------+--------+-------+---------------------+ | id | firstname | lastname | title | age | yearofservice| salary | perks | email | +----+-----------+----------+----------------------------+------+---------------+--------+-------+---------------------+ | 1 | John | Chen | Senior Programmer | 31 | 3| 120000 | 25000 | j@hotmail.ru | | 2 | Jan | Pillai | Senior Programmer | 32 | 4| 110000 | 20000 | g@yahoo.ru | | 3 | Ane | Pandit | Web Designer | 24 | 3| 90000 | 15000 | a@gmail.ru | | 4 | Mary | Anchor | Web Designer | 27 | 2| 85000 | 15000 | m@mail.ru | | 5 | Fred | King | Programmer | 32 | 3| 75000 | 15000 | f@net.ru | | 6 | John | Mac | Programmer | 32 | 4| 80000 | 16000 | j@hotmail.ru | | 7 | Arthur | Sam | Programmer | 28 | 2| 75000 | 14000 | e@yahoo.ru | | 8 | Alok | Nanda | Programmer | 32 | 3| 70000 | 10000 | a@yahoo.ru | | 9 | Susan | Ra | Multimedia Programmer | 32 | 4| 90000 | 15000 | h@gmail.ru | | 10 | Paul | Simon | Multimedia Programmer | 23 | 1| 85000 | 12000 | ps@gmail.ru | | 11 | Edward | Parhar | Multimedia Programmer | 30 | 2| 75000 | 15000 | a@hotmail.ru | | 12 | Kim | Hunter | Senior Web Designer | 32 | 4| 110000 | 20000 | kim@coolmail.ru | | 13 | Roger | Lewis | System Administrator | 32 | 3| 100000 | 13000 | roger@mail.ru | | 14 | Danny | Gibson | System Administrator | 31 | 2| 90000 | 12000 | danny@hotmail.ru | | 15 | Mike | Harper | Senior Marketing Executive | 36 | 1| 120000 | 28000 | m@gmail.ru | | 16 | Mary | Sunday | Marketing Executive | 31 | 5| 90000 | 25000 | monica@bigmail.ru | | 17 | Jack | Sim | Marketing Executive | 27 | 1| 70000 | 18000 | hal@gmail.ru | | 18 | Joe | Irvine | Marketing Executive | 27 | 1| 72000 | 18000 | joseph@hotmail.ru | | 19 | Henry | Ali | Customer Service Manager | 32 | 3| 70000 | 9000 | shahida@hotmail.ru | | 20 | Peter | Champion | Finance Manager | 32 | 2| 120000 | 25000 | peter@yahoo.ru | +----+-----------+----------+----------------------------+------+---------------+--------+-------+---------------------+ 20 rows in set (0.00 sec) mysql> select distinct salary from

   -> employee order by salary DESC;

+--------+ | salary | +--------+ | 120000 | | 110000 | | 100000 | | 90000 | | 85000 | | 80000 | | 75000 | | 72000 | | 70000 | +--------+ 9 rows in set (0.00 sec)

/

Drop table employee; CREATE TABLE employee (

   id int unsigned not null auto_increment primary key,
   firstname varchar(20),
   lastname varchar(20),
   title varchar(30),
   age int,
   yearofservice int,
   salary int,
   perks int,
   email varchar(60)

);

INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("John", "Chen", "Senior Programmer", 31, 3, 120000, 25000, "j@hotmail.ru"); INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Jan", "Pillai", "Senior Programmer", 32, 4, 110000, 20000, "g@yahoo.ru"); INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Ane", "Pandit", "Web Designer", 24, 3, 90000, 15000, "a@gmail.ru"); INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Mary", "Anchor", "Web Designer", 27, 2, 85000, 15000, "m@mail.ru"); INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Fred", "King", "Programmer", 32, 3, 75000, 15000, "f@net.ru"); INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("John", "Mac", "Programmer", 32, 4, 80000, 16000, "j@hotmail.ru"); INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Arthur", "Sam", "Programmer", 28, 2, 75000, 14000, "e@yahoo.ru"); INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Alok", "Nanda", "Programmer", 32, 3, 70000, 10000, "a@yahoo.ru"); INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Susan", "Ra", "Multimedia Programmer", 32, 4, 90000, 15000, "h@gmail.ru"); INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Paul", "Simon", "Multimedia Programmer", 23, 1, 85000, 12000, "ps@gmail.ru"); INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Edward", "Parhar", "Multimedia Programmer", 30, 2, 75000, 15000, "a@hotmail.ru"); INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Kim", "Hunter", "Senior Web Designer", 32, 4, 110000, 20000, "kim@coolmail.ru"); INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Roger", "Lewis", "System Administrator", 32, 3, 100000, 13000, "roger@mail.ru"); INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Danny", "Gibson", "System Administrator", 31, 2, 90000, 12000, "danny@hotmail.ru"); INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Mike", "Harper", "Senior Marketing Executive", 36, 1, 120000, 28000, "m@gmail.ru"); INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Mary", "Sunday", "Marketing Executive", 31, 5, 90000, 25000, "monica@bigmail.ru"); INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Jack", "Sim", "Marketing Executive", 27, 1, 70000, 18000, "hal@gmail.ru"); INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Joe", "Irvine", "Marketing Executive", 27, 1, 72000, 18000, "joseph@hotmail.ru"); INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Henry", "Ali", "Customer Service Manager", 32, 3, 70000, 9000, "shahida@hotmail.ru"); INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Peter", "Champion", "Finance Manager", 32, 2, 120000, 25000, "peter@yahoo.ru"); select * from employee; select distinct salary from employee order by salary DESC;

      </source>

Default sort order is ascending

   <source lang="sql">

/* mysql> Drop table Bird; Query OK, 0 rows affected (0.00 sec) mysql> CREATE TABLE Bird (

   ->     name VARCHAR(20),
   ->     owner VARCHAR(20),
   ->     species VARCHAR(20),
   ->     sex CHAR(1),
   ->     birth DATE,
   ->     death DATE
   -> );

Query OK, 0 rows affected (0.05 sec) mysql> INSERT INTO Bird VALUES ("BlueBird","Joe","Car","f","1999-03-30",NULL); Query OK, 1 row affected (0.00 sec) mysql> INSERT INTO Bird VALUES ("RedBird","Yin","Bus","m","1979-04-30",NULL); Query OK, 1 row affected (0.00 sec) mysql> INSERT INTO Bird VALUES ("RedBird","Yin","Bus","m","1998-01-30",NULL); Query OK, 1 row affected (0.01 sec) mysql> select * from Bird; +----------+-------+---------+------+------------+-------+ | name | owner | species | sex | birth | death | +----------+-------+---------+------+------------+-------+ | BlueBird | Joe | Car | f | 1999-03-30 | NULL | | RedBird | Yin | Bus | m | 1979-04-30 | NULL | | RedBird | Yin | Bus | m | 1998-01-30 | NULL | +----------+-------+---------+------+------------+-------+ 3 rows in set (0.00 sec) mysql> /* To sort a result, use an ORDER BY clause. mysql> default sort order is ascending mysql> */ mysql> SELECT name, birth FROM Bird ORDER BY birth; +----------+------------+ | name | birth | +----------+------------+ | RedBird | 1979-04-30 | | RedBird | 1998-01-30 | | BlueBird | 1999-03-30 | +----------+------------+ 3 rows in set (0.00 sec)

/

Drop table Bird; CREATE TABLE Bird (

   name VARCHAR(20), 
   owner VARCHAR(20),
   species VARCHAR(20), 
   sex CHAR(1), 
   birth DATE, 
   death DATE

);

INSERT INTO Bird VALUES ("BlueBird","Joe","Car","f","1999-03-30",NULL); INSERT INTO Bird VALUES ("RedBird","Yin","Bus","m","1979-04-30",NULL); INSERT INTO Bird VALUES ("RedBird","Yin","Bus","m","1998-01-30",NULL);

select * from Bird;

/* To sort a result, use an ORDER BY clause.

  default sort order is ascending

/

SELECT name, birth FROM Bird ORDER BY birth;

      </source>

Indicate of ascend

   <source lang="sql">

/* mysql> select * from Student; +-----------+------------+-----------+ | StudentID | first_name | last_name | +-----------+------------+-----------+ | 1 | John | Jones | | 2 | Gary | Burton | | 3 | Emily | Scarlett | | 4 | Bruce | Lee | | 5 | Anna | Wolff | | 6 | Vic | Andrews | | 7 | Steve | Alaska | +-----------+------------+-----------+ 7 rows in set (0.02 sec) mysql> select * from Student ORDER BY StudentID DESC; +-----------+------------+-----------+ | StudentID | first_name | last_name | +-----------+------------+-----------+ | 7 | Steve | Alaska | | 6 | Vic | Andrews | | 5 | Anna | Wolff | | 4 | Bruce | Lee | | 3 | Emily | Scarlett | | 2 | Gary | Burton | | 1 | John | Jones | +-----------+------------+-----------+ 7 rows in set (0.01 sec)

/

Drop table Student; CREATE TABLE Student (

  StudentID INT NOT NULL PRIMARY KEY,
  first_name      VARCHAR(50) NOT NULL,
  last_name      VARCHAR(50) NOT NULL

)TYPE = InnoDB;

INSERT INTO Student (StudentID,first_name, last_name) VALUES (4,"Bruce", "Lee"); INSERT INTO Student (StudentID,first_name, last_name) VALUES (1,"John", "Jones"); INSERT INTO Student (StudentID,first_name, last_name) VALUES (2,"Gary", "Burton"); INSERT INTO Student (StudentID,first_name, last_name) VALUES (7,"Steve", "Alaska"); INSERT INTO Student (StudentID,first_name, last_name) VALUES (5,"Anna", "Wolff"); INSERT INTO Student (StudentID,first_name, last_name) VALUES (6,"Vic", "Andrews"); INSERT INTO Student (StudentID,first_name, last_name) VALUES (3,"Emily", "Scarlett"); select * from Student; select * from Student ORDER BY StudentID DESC;

      </source>

Narrow down data with condition and order it

   <source lang="sql">

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

   ->        from employee_person
   ->        where m_status = "Y"
   ->        ORDER BY s_name;

+----+-----------------+ | id | s_name | +----+-----------------+ | 1 | Ane Regina | | 15 | Betty Cudly | | 17 | Edgar Alan | | 2 | Jane Van | | 9 | Man Nanda | | 4 | Manish Sha | | 7 | Mary Shelly | | 13 | Matt Shi | | 11 | Muriel Lovelace | | 12 | Rina Brighton | | 21 | Ruby Richer | | 16 | Stella Stevens | +----+-----------------+ 12 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.ru", "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.ru", "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.ru", "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.ru", "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.ru", "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.ru", "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.ru", "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.ru", "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.ru", "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.ru", "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.ru", "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.ru", "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.ru", "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.ru", "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.ru", "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.ru", "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.ru", "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.ru", "1964-06-13", "M", "Y", "Ruby Richer", 2); select * from employee_person;

select id, s_name

      from employee_person
      where m_status = "Y" 
      ORDER BY s_name;
          
      </source>

Order BY and Limit

   <source lang="sql">

/*mysql> select * from employee; +----+-----------+----------+----------------------------+------+---------------+--------+-------+---------------------+ | id | firstname | lastname | title | age | yearofservice| salary | perks | email | +----+-----------+----------+----------------------------+------+---------------+--------+-------+---------------------+ | 1 | John | Chen | Senior Programmer | 31 | 3| 120000 | 25000 | j@hotmail.ru | | 2 | Jan | Pillai | Senior Programmer | 32 | 4| 110000 | 20000 | g@yahoo.ru | | 3 | Ane | Pandit | Web Designer | 24 | 3| 90000 | 15000 | a@gmail.ru | | 4 | Mary | Anchor | Web Designer | 27 | 2| 85000 | 15000 | m@mail.ru | | 5 | Fred | King | Programmer | 32 | 3| 75000 | 15000 | f@net.ru | | 6 | John | Mac | Programmer | 32 | 4| 80000 | 16000 | j@hotmail.ru | | 7 | Arthur | Sam | Programmer | 28 | 2| 75000 | 14000 | e@yahoo.ru | | 8 | Alok | Nanda | Programmer | 32 | 3| 70000 | 10000 | a@yahoo.ru | | 9 | Susan | Ra | Multimedia Programmer | 32 | 4| 90000 | 15000 | h@gmail.ru | | 10 | Paul | Simon | Multimedia Programmer | 23 | 1| 85000 | 12000 | ps@gmail.ru | | 11 | Edward | Parhar | Multimedia Programmer | 30 | 2| 75000 | 15000 | a@hotmail.ru | | 12 | Kim | Hunter | Senior Web Designer | 32 | 4| 110000 | 20000 | kim@coolmail.ru | | 13 | Roger | Lewis | System Administrator | 32 | 3| 100000 | 13000 | roger@mail.ru | | 14 | Danny | Gibson | System Administrator | 31 | 2| 90000 | 12000 | danny@hotmail.ru | | 15 | Mike | Harper | Senior Marketing Executive | 36 | 1| 120000 | 28000 | m@gmail.ru | | 16 | Mary | Sunday | Marketing Executive | 31 | 5| 90000 | 25000 | monica@bigmail.ru | | 17 | Jack | Sim | Marketing Executive | 27 | 1| 70000 | 18000 | hal@gmail.ru | | 18 | Joe | Irvine | Marketing Executive | 27 | 1| 72000 | 18000 | joseph@hotmail.ru | | 19 | Henry | Ali | Customer Service Manager | 32 | 3| 70000 | 9000 | shahida@hotmail.ru | | 20 | Peter | Champion | Finance Manager | 32 | 2| 120000 | 25000 | peter@yahoo.ru | +----+-----------+----------+----------------------------+------+---------------+--------+-------+---------------------+ 20 rows in set (0.00 sec) mysql> SELECT firstname, lastName, age from

   -> employee ORDER BY age DESC
   -> LIMIT 4;

+-----------+----------+------+ | firstname | lastName | age | +-----------+----------+------+ | Mike | Harper | 36 | | Roger | Lewis | 32 | | Susan | Ra | 32 | | Alok | Nanda | 32 | +-----------+----------+------+ 4 rows in set (0.00 sec)

/

Drop table employee; CREATE TABLE employee (

   id int unsigned not null auto_increment primary key,
   firstname varchar(20),
   lastname varchar(20),
   title varchar(30),
   age int,
   yearofservice int,
   salary int,
   perks int,
   email varchar(60)

);

INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("John", "Chen", "Senior Programmer", 31, 3, 120000, 25000, "j@hotmail.ru"); INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Jan", "Pillai", "Senior Programmer", 32, 4, 110000, 20000, "g@yahoo.ru"); INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Ane", "Pandit", "Web Designer", 24, 3, 90000, 15000, "a@gmail.ru"); INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Mary", "Anchor", "Web Designer", 27, 2, 85000, 15000, "m@mail.ru"); INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Fred", "King", "Programmer", 32, 3, 75000, 15000, "f@net.ru"); INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("John", "Mac", "Programmer", 32, 4, 80000, 16000, "j@hotmail.ru"); INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Arthur", "Sam", "Programmer", 28, 2, 75000, 14000, "e@yahoo.ru"); INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Alok", "Nanda", "Programmer", 32, 3, 70000, 10000, "a@yahoo.ru"); INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Susan", "Ra", "Multimedia Programmer", 32, 4, 90000, 15000, "h@gmail.ru"); INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Paul", "Simon", "Multimedia Programmer", 23, 1, 85000, 12000, "ps@gmail.ru"); INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Edward", "Parhar", "Multimedia Programmer", 30, 2, 75000, 15000, "a@hotmail.ru"); INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Kim", "Hunter", "Senior Web Designer", 32, 4, 110000, 20000, "kim@coolmail.ru"); INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Roger", "Lewis", "System Administrator", 32, 3, 100000, 13000, "roger@mail.ru"); INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Danny", "Gibson", "System Administrator", 31, 2, 90000, 12000, "danny@hotmail.ru"); INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Mike", "Harper", "Senior Marketing Executive", 36, 1, 120000, 28000, "m@gmail.ru"); INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Mary", "Sunday", "Marketing Executive", 31, 5, 90000, 25000, "monica@bigmail.ru"); INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Jack", "Sim", "Marketing Executive", 27, 1, 70000, 18000, "hal@gmail.ru"); INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Joe", "Irvine", "Marketing Executive", 27, 1, 72000, 18000, "joseph@hotmail.ru"); INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Henry", "Ali", "Customer Service Manager", 32, 3, 70000, 9000, "shahida@hotmail.ru"); INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Peter", "Champion", "Finance Manager", 32, 2, 120000, 25000, "peter@yahoo.ru");

SELECT firstname, lastName, age from employee ORDER BY age LIMIT 2;

      </source>

Order decending

   <source lang="sql">

/* mysql> select * from employee; +----+-----------+----------+----------------------------+------+---------------+--------+-------+---------------------+ | id | firstname | lastname | title | age | yearofservice| salary | perks | email | +----+-----------+----------+----------------------------+------+---------------+--------+-------+---------------------+ | 1 | John | Chen | Senior Programmer | 31 | 3| 120000 | 25000 | j@hotmail.ru | | 2 | Jan | Pillai | Senior Programmer | 32 | 4| 110000 | 20000 | g@yahoo.ru | | 3 | Ane | Pandit | Web Designer | 24 | 3| 90000 | 15000 | a@gmail.ru | | 4 | Mary | Anchor | Web Designer | 27 | 2| 85000 | 15000 | m@mail.ru | | 5 | Fred | King | Programmer | 32 | 3| 75000 | 15000 | f@net.ru | | 6 | John | Mac | Programmer | 32 | 4| 80000 | 16000 | j@hotmail.ru | | 7 | Arthur | Sam | Programmer | 28 | 2| 75000 | 14000 | e@yahoo.ru | | 8 | Alok | Nanda | Programmer | 32 | 3| 70000 | 10000 | a@yahoo.ru | | 9 | Susan | Ra | Multimedia Programmer | 32 | 4| 90000 | 15000 | h@gmail.ru | | 10 | Paul | Simon | Multimedia Programmer | 23 | 1| 85000 | 12000 | ps@gmail.ru | | 11 | Edward | Parhar | Multimedia Programmer | 30 | 2| 75000 | 15000 | a@hotmail.ru | | 12 | Kim | Hunter | Senior Web Designer | 32 | 4| 110000 | 20000 | kim@coolmail.ru | | 13 | Roger | Lewis | System Administrator | 32 | 3| 100000 | 13000 | roger@mail.ru | | 14 | Danny | Gibson | System Administrator | 31 | 2| 90000 | 12000 | danny@hotmail.ru | | 15 | Mike | Harper | Senior Marketing Executive | 36 | 1| 120000 | 28000 | m@gmail.ru | | 16 | Mary | Sunday | Marketing Executive | 31 | 5| 90000 | 25000 | monica@bigmail.ru | | 17 | Jack | Sim | Marketing Executive | 27 | 1| 70000 | 18000 | hal@gmail.ru | | 18 | Joe | Irvine | Marketing Executive | 27 | 1| 72000 | 18000 | joseph@hotmail.ru | | 19 | Henry | Ali | Customer Service Manager | 32 | 3| 70000 | 9000 | shahida@hotmail.ru | | 20 | Peter | Champion | Finance Manager | 32 | 2| 120000 | 25000 | peter@yahoo.ru | +----+-----------+----------+----------------------------+------+---------------+--------+-------+---------------------+ 20 rows in set (0.02 sec) mysql> select firstname, lastName, age

   ->        from employee
   ->        order by age DESC limit 1;

+-----------+----------+------+ | firstname | lastName | age | +-----------+----------+------+ | Mike | Harper | 36 | +-----------+----------+------+ 1 row in set (0.00 sec)

/

Drop table employee; CREATE TABLE employee (

   id int unsigned not null auto_increment primary key,
   firstname varchar(20),
   lastname varchar(20),
   title varchar(30),
   age int,
   yearofservice int,
   salary int,
   perks int,
   email varchar(60)

);

INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("John", "Chen", "Senior Programmer", 31, 3, 120000, 25000, "j@hotmail.ru"); INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Jan", "Pillai", "Senior Programmer", 32, 4, 110000, 20000, "g@yahoo.ru"); INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Ane", "Pandit", "Web Designer", 24, 3, 90000, 15000, "a@gmail.ru"); INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Mary", "Anchor", "Web Designer", 27, 2, 85000, 15000, "m@mail.ru"); INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Fred", "King", "Programmer", 32, 3, 75000, 15000, "f@net.ru"); INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("John", "Mac", "Programmer", 32, 4, 80000, 16000, "j@hotmail.ru"); INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Arthur", "Sam", "Programmer", 28, 2, 75000, 14000, "e@yahoo.ru"); INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Alok", "Nanda", "Programmer", 32, 3, 70000, 10000, "a@yahoo.ru"); INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Susan", "Ra", "Multimedia Programmer", 32, 4, 90000, 15000, "h@gmail.ru"); INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Paul", "Simon", "Multimedia Programmer", 23, 1, 85000, 12000, "ps@gmail.ru"); INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Edward", "Parhar", "Multimedia Programmer", 30, 2, 75000, 15000, "a@hotmail.ru"); INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Kim", "Hunter", "Senior Web Designer", 32, 4, 110000, 20000, "kim@coolmail.ru"); INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Roger", "Lewis", "System Administrator", 32, 3, 100000, 13000, "roger@mail.ru"); INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Danny", "Gibson", "System Administrator", 31, 2, 90000, 12000, "danny@hotmail.ru"); INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Mike", "Harper", "Senior Marketing Executive", 36, 1, 120000, 28000, "m@gmail.ru"); INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Mary", "Sunday", "Marketing Executive", 31, 5, 90000, 25000, "monica@bigmail.ru"); INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Jack", "Sim", "Marketing Executive", 27, 1, 70000, 18000, "hal@gmail.ru"); INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Joe", "Irvine", "Marketing Executive", 27, 1, 72000, 18000, "joseph@hotmail.ru"); INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Henry", "Ali", "Customer Service Manager", 32, 3, 70000, 9000, "shahida@hotmail.ru"); INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Peter", "Champion", "Finance Manager", 32, 2, 120000, 25000, "peter@yahoo.ru");

select * from employee; select firstname, lastName, age

      from employee
      order by age DESC limit 1;

      </source>

Order result wiht ORDER

   <source lang="sql">

/* mysql> select * from Student; +-----------+------------+-----------+ | StudentID | first_name | last_name | +-----------+------------+-----------+ | 1 | John | Jones | | 2 | Gary | Burton | | 3 | Emily | Scarlett | | 4 | Bruce | Lee | | 5 | Anna | Wolff | | 6 | Vic | Andrews | | 7 | Steve | Alaska | +-----------+------------+-----------+ 7 rows in set (0.01 sec) mysql> select * from Student WHERE StudentID != 8 ORDER BY studentID; +-----------+------------+-----------+ | StudentID | first_name | last_name | +-----------+------------+-----------+ | 1 | John | Jones | | 2 | Gary | Burton | | 3 | Emily | Scarlett | | 4 | Bruce | Lee | | 5 | Anna | Wolff | | 6 | Vic | Andrews | | 7 | Steve | Alaska | +-----------+------------+-----------+ 7 rows in set (0.00 sec)

/

Drop table Student; CREATE TABLE Student (

  StudentID INT NOT NULL PRIMARY KEY,
  first_name      VARCHAR(50) NOT NULL,
  last_name      VARCHAR(50) NOT NULL

)TYPE = InnoDB;

INSERT INTO Student (StudentID,first_name, last_name) VALUES (4,"Bruce", "Lee"); INSERT INTO Student (StudentID,first_name, last_name) VALUES (1,"John", "Jones"); INSERT INTO Student (StudentID,first_name, last_name) VALUES (2,"Gary", "Burton"); INSERT INTO Student (StudentID,first_name, last_name) VALUES (7,"Steve", "Alaska"); INSERT INTO Student (StudentID,first_name, last_name) VALUES (5,"Anna", "Wolff"); INSERT INTO Student (StudentID,first_name, last_name) VALUES (6,"Vic", "Andrews"); INSERT INTO Student (StudentID,first_name, last_name) VALUES (3,"Emily", "Scarlett"); select * from Student;

select * from Student WHERE StudentID != 8 ORDER BY studentID;

      </source>

Order row in select clause

   <source lang="sql">

/* mysql> select * from employee; +----+-----------+----------+----------------------------+------+---------------+--------+-------+---------------------+ | id | firstname | lastname | title | age | yearofservice| salary | perks | email | +----+-----------+----------+----------------------------+------+---------------+--------+-------+---------------------+ | 1 | John | Chen | Senior Programmer | 31 | 3| 120000 | 25000 | j@hotmail.ru | | 2 | Jan | Pillai | Senior Programmer | 32 | 4| 110000 | 20000 | g@yahoo.ru | | 3 | Ane | Pandit | Web Designer | 24 | 3| 90000 | 15000 | a@gmail.ru | | 4 | Mary | Anchor | Web Designer | 27 | 2| 85000 | 15000 | m@mail.ru | | 5 | Fred | King | Programmer | 32 | 3| 75000 | 15000 | f@net.ru | | 6 | John | Mac | Programmer | 32 | 4| 80000 | 16000 | j@hotmail.ru | | 7 | Arthur | Sam | Programmer | 28 | 2| 75000 | 14000 | e@yahoo.ru | | 8 | Alok | Nanda | Programmer | 32 | 3| 70000 | 10000 | a@yahoo.ru | | 9 | Susan | Ra | Multimedia Programmer | 32 | 4| 90000 | 15000 | h@gmail.ru | | 10 | Paul | Simon | Multimedia Programmer | 23 | 1| 85000 | 12000 | ps@gmail.ru | | 11 | Edward | Parhar | Multimedia Programmer | 30 | 2| 75000 | 15000 | a@hotmail.ru | | 12 | Kim | Hunter | Senior Web Designer | 32 | 4| 110000 | 20000 | kim@coolmail.ru | | 13 | Roger | Lewis | System Administrator | 32 | 3| 100000 | 13000 | roger@mail.ru | | 14 | Danny | Gibson | System Administrator | 31 | 2| 90000 | 12000 | danny@hotmail.ru | | 15 | Mike | Harper | Senior Marketing Executive | 36 | 1| 120000 | 28000 | m@gmail.ru | | 16 | Mary | Sunday | Marketing Executive | 31 | 5| 90000 | 25000 | monica@bigmail.ru | | 17 | Jack | Sim | Marketing Executive | 27 | 1| 70000 | 18000 | hal@gmail.ru | | 18 | Joe | Irvine | Marketing Executive | 27 | 1| 72000 | 18000 | joseph@hotmail.ru | | 19 | Henry | Ali | Customer Service Manager | 32 | 3| 70000 | 9000 | shahida@hotmail.ru | | 20 | Peter | Champion | Finance Manager | 32 | 2| 120000 | 25000 | peter@yahoo.ru | +----+-----------+----------+----------------------------+------+---------------+--------+-------+---------------------+ 20 rows in set (0.00 sec) mysql> SELECT firstname, lastName, salary

   ->        from employee
   ->        ORDER BY salary;

+-----------+----------+--------+ | firstname | lastName | salary | +-----------+----------+--------+ | Jack | Sim | 70000 | | Henry | Ali | 70000 | | Alok | Nanda | 70000 | | Joe | Irvine | 72000 | | Edward | Parhar | 75000 | | Arthur | Sam | 75000 | | Fred | King | 75000 | | John | Mac | 80000 | | Mary | Anchor | 85000 | | Paul | Simon | 85000 | | Mary | Sunday | 90000 | | Danny | Gibson | 90000 | | Susan | Ra | 90000 | | Ane | Pandit | 90000 | | Roger | Lewis | 100000 | | Jan | Pillai | 110000 | | Kim | Hunter | 110000 | | Mike | Harper | 120000 | | John | Chen | 120000 | | Peter | Champion | 120000 | +-----------+----------+--------+ 20 rows in set (0.01 sec)

/

Drop table employee; CREATE TABLE employee (

   id int unsigned not null auto_increment primary key,
   firstname varchar(20),
   lastname varchar(20),
   title varchar(30),
   age int,
   yearofservice int,
   salary int,
   perks int,
   email varchar(60)

);

INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("John", "Chen", "Senior Programmer", 31, 3, 120000, 25000, "j@hotmail.ru"); INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Jan", "Pillai", "Senior Programmer", 32, 4, 110000, 20000, "g@yahoo.ru"); INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Ane", "Pandit", "Web Designer", 24, 3, 90000, 15000, "a@gmail.ru"); INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Mary", "Anchor", "Web Designer", 27, 2, 85000, 15000, "m@mail.ru"); INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Fred", "King", "Programmer", 32, 3, 75000, 15000, "f@net.ru"); INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("John", "Mac", "Programmer", 32, 4, 80000, 16000, "j@hotmail.ru"); INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Arthur", "Sam", "Programmer", 28, 2, 75000, 14000, "e@yahoo.ru"); INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Alok", "Nanda", "Programmer", 32, 3, 70000, 10000, "a@yahoo.ru"); INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Susan", "Ra", "Multimedia Programmer", 32, 4, 90000, 15000, "h@gmail.ru"); INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Paul", "Simon", "Multimedia Programmer", 23, 1, 85000, 12000, "ps@gmail.ru"); INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Edward", "Parhar", "Multimedia Programmer", 30, 2, 75000, 15000, "a@hotmail.ru"); INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Kim", "Hunter", "Senior Web Designer", 32, 4, 110000, 20000, "kim@coolmail.ru"); INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Roger", "Lewis", "System Administrator", 32, 3, 100000, 13000, "roger@mail.ru"); INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Danny", "Gibson", "System Administrator", 31, 2, 90000, 12000, "danny@hotmail.ru"); INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Mike", "Harper", "Senior Marketing Executive", 36, 1, 120000, 28000, "m@gmail.ru"); INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Mary", "Sunday", "Marketing Executive", 31, 5, 90000, 25000, "monica@bigmail.ru"); INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Jack", "Sim", "Marketing Executive", 27, 1, 70000, 18000, "hal@gmail.ru"); INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Joe", "Irvine", "Marketing Executive", 27, 1, 72000, 18000, "joseph@hotmail.ru"); INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Henry", "Ali", "Customer Service Manager", 32, 3, 70000, 9000, "shahida@hotmail.ru"); INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Peter", "Champion", "Finance Manager", 32, 2, 120000, 25000, "peter@yahoo.ru"); select * from employee; SELECT firstname, lastName, salary

      from employee 
      ORDER BY salary;

      </source>

Order two columns with different orders

   <source lang="sql">

Drop table employee; CREATE TABLE employee (

   id int unsigned not null auto_increment primary key,
   firstname varchar(20),
   lastname varchar(20),
   title varchar(30),
   age int,
   yearofservice int,
   salary int,
   perks int,
   email varchar(60)

);

INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("John", "Chen", "Senior Programmer", 31, 3, 120000, 25000, "j@hotmail.ru"); INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Jan", "Pillai", "Senior Programmer", 32, 4, 110000, 20000, "g@yahoo.ru"); INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Ane", "Pandit", "Web Designer", 24, 3, 90000, 15000, "a@gmail.ru"); INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Mary", "Anchor", "Web Designer", 27, 2, 85000, 15000, "m@mail.ru"); INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Fred", "King", "Programmer", 32, 3, 75000, 15000, "f@net.ru"); INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("John", "Mac", "Programmer", 32, 4, 80000, 16000, "j@hotmail.ru"); INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Arthur", "Sam", "Programmer", 28, 2, 75000, 14000, "e@yahoo.ru"); INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Alok", "Nanda", "Programmer", 32, 3, 70000, 10000, "a@yahoo.ru"); INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Susan", "Ra", "Multimedia Programmer", 32, 4, 90000, 15000, "h@gmail.ru"); INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Paul", "Simon", "Multimedia Programmer", 23, 1, 85000, 12000, "ps@gmail.ru"); INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Edward", "Parhar", "Multimedia Programmer", 30, 2, 75000, 15000, "a@hotmail.ru"); INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Kim", "Hunter", "Senior Web Designer", 32, 4, 110000, 20000, "kim@coolmail.ru"); INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Roger", "Lewis", "System Administrator", 32, 3, 100000, 13000, "roger@mail.ru"); INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Danny", "Gibson", "System Administrator", 31, 2, 90000, 12000, "danny@hotmail.ru"); INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Mike", "Harper", "Senior Marketing Executive", 36, 1, 120000, 28000, "m@gmail.ru"); INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Mary", "Sunday", "Marketing Executive", 31, 5, 90000, 25000, "monica@bigmail.ru"); INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Jack", "Sim", "Marketing Executive", 27, 1, 70000, 18000, "hal@gmail.ru"); INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Joe", "Irvine", "Marketing Executive", 27, 1, 72000, 18000, "joseph@hotmail.ru"); INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Henry", "Ali", "Customer Service Manager", 32, 3, 70000, 9000, "shahida@hotmail.ru"); INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Peter", "Champion", "Finance Manager", 32, 2, 120000, 25000, "peter@yahoo.ru"); select * from employee;

/*The statement displays the ids, last names, titles and ages of all employees sorted on titles in descending order and age in ascending order.

/

SELECT id, lastName, title, age

      from employee ORDER BY
      title DESC, age ASC;

      </source>

Simple ORDER by

   <source lang="sql">

/* mysql> select * from employee_person; +----+-------------------------------------+---------+---------------------------+------------+------+----------+-----------------+----------+ | id | address | phone | email | birthday | sex | m_status | s_name | children | +----+-------------------------------------+---------+---------------------------+------------+------+----------+-----------------+----------+ | 1 | 200, Regina Street | 7176666 | net@hotmail.ru | 1971-04-26 | M | Y | Ane Regina | NULL | | 2 | 1232 Alberta Road | 5553312 | jo@hotmail.ru | 1968-03-02 | M | Y | Jane Van | 3 | | 3 | 90 Potter A | 3331211 | gp@ymail.ru | 1967-09-22 | M | N | Sandhya Pil | 2 | | 4 | 202, Donna Street | 7176167 | twink@hotmail.ru | 1976-08-09 | F | Y | Manish Sha | NULL | | 5 | Apartment #8, UBC, Van Street | 8973242 | holy@ymail.ru | 1974-10-14 | F | N | NULL | NULL | | 6 | 46 SFU Street | 6451234 | kill@el.ru | 1978-12-31 | M | N | NULL | NULL | | 7 | 432 Mercury Ave | 7932232 | mac@hotmail.ru | 1966-08-21 | M | Y | Mary Shelly | 3 | | 8 | 8 Little YaleTown | 5442994 | edd@gmail.ru | 1975-01-14 | M | N | NULL | NULL | | 9 | 64 Temp Road | 4327652 | nan@pmail.ru | 1969-05-19 | M | Y | Man Nanda | 1 | | 10 | 132 Metro House, Henry Street | 5552376 | ra@hotmail.ru | 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.ru | 1968-09-03 | F | Y | Matt Shi | 2 | | 14 | 76 Fish Street | 7432433 | tink@email.ru | 1965-04-28 | M | N | NULL | NULL | | 15 | 98 Gun Street | 6500787 | danny@fhardy.ru | 1966-06-23 | M | Y | Betty Cudly | 3 | | 16 | #5 Winnepag Homes | 5433243 | mike@cmail.ru | 1964-03-06 | M | Y | Stella Stevens | 2 | | 17 | 652 Devon Building, 6th Jade Avenue | 5537885 | mona@darling.ru | 1970-04-18 | F | Y | Edgar Alan | 1 | | 18 | Apartment #9, Together Towers | 5476565 | odessey@hotmail.ru | 1973-10-09 | M | N | NULL | NULL | | 19 | Apartment #9, West Towers | 5476565 | jire@hotmail.ru | 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.ru | 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.ru", "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.ru", "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.ru", "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.ru", "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.ru", "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.ru", "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.ru", "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.ru", "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.ru", "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.ru", "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.ru", "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.ru", "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.ru", "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.ru", "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.ru", "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.ru", "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.ru", "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.ru", "1964-06-13", "M", "Y", "Ruby Richer", 2); select * from employee_person; select id, birthday from employee_person ORDER BY birthday;

      </source>

Sort columns in different directions

   <source lang="sql">

/* mysql> Drop table Bird; Query OK, 0 rows affected (0.01 sec) mysql> CREATE TABLE Bird (

   ->     name VARCHAR(20),
   ->     owner VARCHAR(20),
   ->     species VARCHAR(20),
   ->     sex CHAR(1),
   ->     birth DATE,
   ->     death DATE
   -> );

Query OK, 0 rows affected (0.06 sec) mysql> INSERT INTO Bird VALUES ("BlueBird","Joe","Car","f","1999-03-30",NULL); Query OK, 1 row affected (0.00 sec) mysql> INSERT INTO Bird VALUES ("RedBird","Yin","Bus","m","1979-04-30",NULL); Query OK, 1 row affected (0.01 sec) mysql> INSERT INTO Bird VALUES ("RedBird","Yin","Bus","m","1998-01-30",NULL); Query OK, 1 row affected (0.00 sec) mysql> select * from Bird; +----------+-------+---------+------+------------+-------+ | name | owner | species | sex | birth | death | +----------+-------+---------+------+------------+-------+ | BlueBird | Joe | Car | f | 1999-03-30 | NULL | | RedBird | Yin | Bus | m | 1979-04-30 | NULL | | RedBird | Yin | Bus | m | 1998-01-30 | NULL | +----------+-------+---------+------+------------+-------+ 3 rows in set (0.00 sec) mysql> SELECT name, species, birth FROM Bird

   ->       ORDER BY species, birth DESC;

+----------+---------+------------+ | name | species | birth | +----------+---------+------------+ | RedBird | Bus | 1998-01-30 | | RedBird | Bus | 1979-04-30 | | BlueBird | Car | 1999-03-30 | +----------+---------+------------+ 3 rows in set (0.00 sec)

/

Drop table Bird; CREATE TABLE Bird (

   name VARCHAR(20), 
   owner VARCHAR(20),
   species VARCHAR(20), 
   sex CHAR(1), 
   birth DATE, 
   death DATE

);

INSERT INTO Bird VALUES ("BlueBird","Joe","Car","f","1999-03-30",NULL); INSERT INTO Bird VALUES ("RedBird","Yin","Bus","m","1979-04-30",NULL); INSERT INTO Bird VALUES ("RedBird","Yin","Bus","m","1998-01-30",NULL);

select * from Bird;

SELECT name, species, birth FROM Bird

     ORDER BY species, birth DESC;
          
      </source>

Sorting Data

   <source lang="sql">

/* mysql> SELECT StudentID,ExamID,Mark,IfPassed FROM Exam

   -> ORDER BY Mark ASC;

+-----------+--------+------+----------+ | StudentID | ExamID | Mark | IfPassed | +-----------+--------+------+----------+ | 2 | 3 | 44 | 1 | | 1 | 1 | 55 | 1 | | 1 | 2 | 73 | 0 | +-----------+--------+------+----------+ 3 rows in set (0.00 sec)

/

/* Prepare the data */ Drop TABLE Exam; CREATE TABLE Exam (

  StudentID  INT NOT NULL,
  ExamID     INT NOT NULL,
  Mark       INT,
  IfPassed   SMALLINT

)TYPE = InnoDB; /* Insert data for testing */ INSERT INTO Exam (StudentID,ExamID,Mark,IfPassed) VALUES (1,1,55,1); INSERT INTO Exam (StudentID,ExamID,Mark,IfPassed) VALUES (1,2,73,0); INSERT INTO Exam (StudentID,ExamID,Mark,IfPassed) VALUES (2,3,44,1); /* Real command */

SELECT StudentID,ExamID,Mark,IfPassed FROM Exam ORDER BY Mark ASC;

      </source>

Sorting Rows

   <source lang="sql">

/* mysql> Drop table Bird; Query OK, 0 rows affected (0.01 sec) mysql> CREATE TABLE Bird (

   ->     name VARCHAR(20),
   ->     owner VARCHAR(20),
   ->     species VARCHAR(20),
   ->     sex CHAR(1),
   ->     birth DATE,
   ->     death DATE
   -> );

Query OK, 0 rows affected (0.07 sec) mysql> INSERT INTO Bird VALUES ("BlueBird","Joe","Car","f","1999-03-30",NULL); Query OK, 1 row affected (0.00 sec) mysql> INSERT INTO Bird VALUES ("RedBird","Yin","Bus","m","1979-03-30",NULL); Query OK, 1 row affected (0.00 sec) mysql> INSERT INTO Bird VALUES ("RedBird","Yin","Bus","m","1979-03-30",NULL); Query OK, 1 row affected (0.00 sec) mysql> select * from Bird; +----------+-------+---------+------+------------+-------+ | name | owner | species | sex | birth | death | +----------+-------+---------+------+------------+-------+ | BlueBird | Joe | Car | f | 1999-03-30 | NULL | | RedBird | Yin | Bus | m | 1979-03-30 | NULL | | RedBird | Yin | Bus | m | 1979-03-30 | NULL | +----------+-------+---------+------+------------+-------+ 3 rows in set (0.00 sec) mysql> SELECT name, species, birth FROM Bird

   -> WHERE species = "Car" OR species = "Bus";

+----------+---------+------------+ | name | species | birth | +----------+---------+------------+ | BlueBird | Car | 1999-03-30 | | RedBird | Bus | 1979-03-30 | | RedBird | Bus | 1979-03-30 | +----------+---------+------------+ 3 rows in set (0.00 sec)

/

Drop table Bird; CREATE TABLE Bird (

   name VARCHAR(20), 
   owner VARCHAR(20),
   species VARCHAR(20), 
   sex CHAR(1), 
   birth DATE, 
   death DATE

);

INSERT INTO Bird VALUES ("BlueBird","Joe","Car","f","1999-03-30",NULL); INSERT INTO Bird VALUES ("RedBird","Yin","Bus","m","1979-04-30",NULL); INSERT INTO Bird VALUES ("RedBird","Yin","Bus","m","1998-01-30",NULL);

select * from Bird;

/* To sort a result, use an ORDER BY clause.

  default sort order is ascending

/

SELECT name, birth FROM Bird ORDER BY birth;

      </source>

Sort on multiple columns

   <source lang="sql">

/* mysql> Drop table Bird; Query OK, 0 rows affected (0.00 sec) mysql> CREATE TABLE Bird (

   ->     name VARCHAR(20),
   ->     owner VARCHAR(20),
   ->     species VARCHAR(20),
   ->     sex CHAR(1),
   ->     birth DATE,
   ->     death DATE
   -> );

Query OK, 0 rows affected (0.05 sec) mysql> INSERT INTO Bird VALUES ("BlueBird","Joe","Car","f","1999-03-30",NULL); Query OK, 1 row affected (0.00 sec) mysql> INSERT INTO Bird VALUES ("RedBird","Yin","Bus","m","1979-04-30",NULL); Query OK, 1 row affected (0.00 sec) mysql> INSERT INTO Bird VALUES ("RedBird","Yin","Bus","m","1998-01-30",NULL); Query OK, 1 row affected (0.00 sec) mysql> select * from Bird; +----------+-------+---------+------+------------+-------+ | name | owner | species | sex | birth | death | +----------+-------+---------+------+------------+-------+ | BlueBird | Joe | Car | f | 1999-03-30 | NULL | | RedBird | Yin | Bus | m | 1979-04-30 | NULL | | RedBird | Yin | Bus | m | 1998-01-30 | NULL | +----------+-------+---------+------+------------+-------+ 3 rows in set (0.00 sec) mysql> SELECT name, species, birth FROM Bird

   ->       ORDER BY species, birth DESC;

+----------+---------+------------+ | name | species | birth | +----------+---------+------------+ | RedBird | Bus | 1998-01-30 | | RedBird | Bus | 1979-04-30 | | BlueBird | Car | 1999-03-30 | +----------+---------+------------+ 3 rows in set (0.00 sec)

/

Drop table Bird; CREATE TABLE Bird (

   name VARCHAR(20), 
   owner VARCHAR(20),
   species VARCHAR(20), 
   sex CHAR(1), 
   birth DATE, 
   death DATE

);

INSERT INTO Bird VALUES ("BlueBird","Joe","Car","f","1999-03-30",NULL); INSERT INTO Bird VALUES ("RedBird","Yin","Bus","m","1979-04-30",NULL); INSERT INTO Bird VALUES ("RedBird","Yin","Bus","m","1998-01-30",NULL);

select * from Bird;

SELECT name, species, birth FROM Bird

     ORDER BY species, birth DESC;

      </source>

To sort in reverse (descending) order

   <source lang="sql">

/* mysql> Drop table Bird; Query OK, 0 rows affected (0.00 sec) mysql> CREATE TABLE Bird (

   ->     name VARCHAR(20),
   ->     owner VARCHAR(20),
   ->     species VARCHAR(20),
   ->     sex CHAR(1),
   ->     birth DATE,
   ->     death DATE
   -> );

Query OK, 0 rows affected (0.04 sec) mysql> INSERT INTO Bird VALUES ("BlueBird","Joe","Car","f","1999-03-30",NULL); Query OK, 1 row affected (0.01 sec) mysql> INSERT INTO Bird VALUES ("RedBird","Yin","Bus","m","1979-04-30",NULL); Query OK, 1 row affected (0.00 sec) mysql> INSERT INTO Bird VALUES ("RedBird","Yin","Bus","m","1998-01-30",NULL); Query OK, 1 row affected (0.00 sec) mysql> select * from Bird; +----------+-------+---------+------+------------+-------+ | name | owner | species | sex | birth | death | +----------+-------+---------+------+------------+-------+ | BlueBird | Joe | Car | f | 1999-03-30 | NULL | | RedBird | Yin | Bus | m | 1979-04-30 | NULL | | RedBird | Yin | Bus | m | 1998-01-30 | NULL | +----------+-------+---------+------+------------+-------+ 3 rows in set (0.00 sec) mysql> /* mysql> add the DESC keyword to the name of the column you are sorting by: mysql> */ mysql> SELECT name, birth FROM Bird ORDER BY birth DESC; +----------+------------+ | name | birth | +----------+------------+ | BlueBird | 1999-03-30 | | RedBird | 1998-01-30 | | RedBird | 1979-04-30 | +----------+------------+ 3 rows in set (0.00 sec)

/

Drop table Bird; CREATE TABLE Bird (

   name VARCHAR(20), 
   owner VARCHAR(20),
   species VARCHAR(20), 
   sex CHAR(1), 
   birth DATE, 
   death DATE

);

INSERT INTO Bird VALUES ("BlueBird","Joe","Car","f","1999-03-30",NULL); INSERT INTO Bird VALUES ("RedBird","Yin","Bus","m","1979-04-30",NULL); INSERT INTO Bird VALUES ("RedBird","Yin","Bus","m","1998-01-30",NULL);

select * from Bird;

/* add the DESC keyword to the name of the column you are sorting by:

/

SELECT name, birth FROM Bird ORDER BY birth DESC;

      </source>

Use ORDER BY to list

   <source lang="sql">

/* mysql> select * from sales; +-----+------------+--------+--------+--------+------+------------+ | num | name | winter | spring | summer | fall | category | +-----+------------+--------+--------+--------+------+------------+ | 1 | Java | 1067 | 200 | 150 | 267 | Holiday | | 2 | C | 970 | 770 | 531 | 486 | Profession | | 3 | JavaScript | 53 | 13 | 21 | 856 | Literary | | 4 | SQL | 782 | 357 | 168 | 250 | Profession | | 5 | Oracle | 589 | 795 | 367 | 284 | Holiday | | 6 | MySQL | 953 | 582 | 336 | 489 | Literary | | 7 | Cplus | 752 | 657 | 259 | 478 | Literary | | 8 | Python | 67 | 23 | 83 | 543 | Holiday | | 9 | PHP | 673 | 48 | 625 | 52 | Profession | +-----+------------+--------+--------+--------+------+------------+ 9 rows in set (0.15 sec) mysql> /* Use ORDER BY to list all the designs in duck_sales arranged by their c ategory. mysql> */ mysql> SELECT name, category FROM sales

   -> ORDER BY category;

/

Drop table sales;

CREATE TABLE sales(

   num MEDIUMINT NOT NULL AUTO_INCREMENT,
   name CHAR(20),
   winter INT,
   spring INT,
   summer INT,
   fall INT,
   category CHAR(13),
   primary key(num)

)type=MyISAM; insert into sales value(1, "Java", 1067 , 200, 150, 267,"Holiday"); insert into sales value(2, "C",970,770,531,486,"Profession"); insert into sales value(3, "JavaScript",53,13,21,856,"Literary"); insert into sales value(4, "SQL",782,357,168,250,"Profession"); insert into sales value(5, "Oracle",589,795,367,284,"Holiday"); insert into sales value(6, "MySQL",953,582,336,489,"Literary"); insert into sales value(7, "Cplus",752,657,259,478,"Literary"); insert into sales value(8, "Python",67,23,83,543,"Holiday"); insert into sales value(9, "PHP",673,48,625,52,"Profession");

select * from sales;

/* Use ORDER BY to list all the designs in duck_sales arranged by their category.

*/

SELECT name, category FROM sales ORDER BY category;

      </source>

Use order by to sort the result

   <source lang="sql">

/* mysql> Drop table Item; Query OK, 0 rows affected (0.00 sec) mysql> CREATE TABLE Item

   -> (
   ->    ID SMALLINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
   ->    Name VARCHAR(50) NOT NULL,
   ->    InStock SMALLINT UNSIGNED NOT NULL,
   ->    OnOrder SMALLINT UNSIGNED NOT NULL,
   ->    Reserved SMALLINT UNSIGNED NOT NULL,
   ->    Department ENUM("Classical", "Popular") NOT NULL,
   ->    Category VARCHAR(20) NOT NULL,
   ->    RowUpdate TIMESTAMP NOT NULL
   -> );

Query OK, 0 rows affected (0.14 sec) mysql> INSERT INTO Item (Name, InStock, OnOrder, Reserved, Department, Category)

   ->           VALUES ("Bloodshot",      10,      5,       1, "Popular",

"Rock"),

   ->                  ("Most",           10,      5,       2, "Classical",

"Opera"),

   ->                  ("Jazz",           17,      4,       3, "Popular",

"Jazz"),

   ->                  ("Class",           9,      4,       4, "Classical",

"Dance"),

   ->                  ("Violin",         24,      2,       5, "Classical",

"General"),

   ->                  ("Cha Cha",        16,      6,       6, "Classical",

"Vocal"),

   ->                  ("Blues",           2,     25,       7, "Popular",

"Blues"),

   ->                  ("Pure",           32,      3,      18, "Popular",

"Jazz"),

   ->                  ("Mud",            12,     15,      19, "Popular",

"Country"),

   ->                  ("The",             5,     20,      11, "Popular",

"New Age"),

   ->                  ("Embrace",        24,     11,      12, "Popular",

"New Age"),

   ->                  ("Magic",          42,     17,      13, "Classical",

"General"),

   ->                  ("Lake",           25,     44,      24, "Classical",

"Dance"),

   ->                  ("LaLala",         20,     10,       5, "Classical",

"Opera"),

   ->                  ("Soul",           15,     30,      16, "Popular",

"Blues"),

   ->                  ("Stages",         42,      0,       7, "Popular",

"Blues"),

   ->                  ("Six",            16,      8,       6, "Classical",

"General"); Query OK, 17 rows affected (0.00 sec) Records: 17 Duplicates: 0 Warnings: 0 mysql> select * from Item; +----+-----------+---------+---------+----------+------------+----------+---------------------+ | ID | Name | InStock | OnOrder | Reserved | Department | Category | RowUpdate | +----+-----------+---------+---------+----------+------------+----------+---------------------+ | 1 | Bloodshot | 10 | 5 | 1 | Popular | Rock | 2005-10-09 09:19:48 | | 2 | Most | 10 | 5 | 2 | Classical | Opera | 2005-10-09 09:19:48 | | 3 | Jazz | 17 | 4 | 3 | Popular | Jazz | 2005-10-09 09:19:48 | | 4 | Class | 9 | 4 | 4 | Classical | Dance | 2005-10-09 09:19:48 | | 5 | Violin | 24 | 2 | 5 | Classical | General | 2005-10-09 09:19:48 | | 6 | Cha Cha | 16 | 6 | 6 | Classical | Vocal | 2005-10-09 09:19:48 | | 7 | Blues | 2 | 25 | 7 | Popular | Blues | 2005-10-09 09:19:48 | | 8 | Pure | 32 | 3 | 18 | Popular | Jazz | 2005-10-09 09:19:48 | | 9 | Mud | 12 | 15 | 19 | Popular | Country | 2005-10-09 09:19:48 | | 10 | The | 5 | 20 | 11 | Popular | New Age | 2005-10-09 09:19:48 | | 11 | Embrace | 24 | 11 | 12 | Popular | New Age | 2005-10-09 09:19:48 | | 12 | Magic | 42 | 17 | 13 | Classical | General | 2005-10-09 09:19:48 | | 13 | Lake | 25 | 44 | 24 | Classical | Dance | 2005-10-09 09:19:48 | | 14 | LaLala | 20 | 10 | 5 | Classical | Opera | 2005-10-09 09:19:48 | | 15 | Soul | 15 | 30 | 16 | Popular | Blues | 2005-10-09 09:19:48 | | 16 | Stages | 42 | 0 | 7 | Popular | Blues | 2005-10-09 09:19:48 | | 17 | Six | 16 | 8 | 6 | Classical | General | 2005-10-09 09:19:48 | +----+-----------+---------+---------+----------+------------+----------+---------------------+ 17 rows in set (0.00 sec) mysql> SELECT Name, InStock, OnOrder

   -> FROM Item
   -> WHERE InStock>20
   -> ORDER BY Name DESC;

+---------+---------+---------+ | Name | InStock | OnOrder | +---------+---------+---------+ | Violin | 24 | 2 | | Stages | 42 | 0 | | Pure | 32 | 3 | | Magic | 42 | 17 | | Lake | 25 | 44 | | Embrace | 24 | 11 | +---------+---------+---------+ 6 rows in set (0.00 sec)

/

Drop table Item; CREATE TABLE Item (

  ID SMALLINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  Name VARCHAR(50) NOT NULL,
  InStock SMALLINT UNSIGNED NOT NULL,
  OnOrder SMALLINT UNSIGNED NOT NULL,
  Reserved SMALLINT UNSIGNED NOT NULL,
  Department ENUM("Classical", "Popular") NOT NULL,
  Category VARCHAR(20) NOT NULL,
  RowUpdate TIMESTAMP NOT NULL

);

INSERT INTO Item (Name, InStock, OnOrder, Reserved, Department, Category)

         VALUES ("Bloodshot",      10,      5,       1, "Popular",      "Rock"),
                ("Most",           10,      5,       2, "Classical",    "Opera"),
                ("Jazz",           17,      4,       3, "Popular",      "Jazz"),
                ("Class",           9,      4,       4, "Classical",    "Dance"),
                ("Violin",         24,      2,       5, "Classical",    "General"),
                ("Cha Cha",        16,      6,       6, "Classical",    "Vocal"),
                ("Blues",           2,     25,       7, "Popular",      "Blues"),
                ("Pure",           32,      3,      18, "Popular",      "Jazz"),
                ("Mud",            12,     15,      19, "Popular",      "Country"),
                ("The",             5,     20,      11, "Popular",      "New Age"),
                ("Embrace",        24,     11,      12, "Popular",      "New Age"),
                ("Magic",          42,     17,      13, "Classical",    "General"),
                ("Lake",           25,     44,      24, "Classical",    "Dance"),
                ("LaLala",         20,     10,       5, "Classical",    "Opera"),
                ("Soul",           15,     30,      16, "Popular",      "Blues"),
                ("Stages",         42,      0,       7, "Popular",      "Blues"),
                ("Six",            16,      8,       6, "Classical",    "General");

select * from Item; SELECT Name, InStock, OnOrder FROM Item WHERE InStock>20 ORDER BY Name DESC;

      </source>

Use two ORDER BY fields

   <source lang="sql">

/* mysql> select * from sales; +-----+------------+--------+--------+--------+------+------------+ | num | name | winter | spring | summer | fall | category | +-----+------------+--------+--------+--------+------+------------+ | 1 | Java | 1067 | 200 | 150 | 267 | Holiday | | 2 | C | 970 | 770 | 531 | 486 | Profession | | 3 | JavaScript | 53 | 13 | 21 | 856 | Literary | | 4 | SQL | 782 | 357 | 168 | 250 | Profession | | 5 | Oracle | 589 | 795 | 367 | 284 | Holiday | | 6 | MySQL | 953 | 582 | 336 | 489 | Literary | | 7 | Cplus | 752 | 657 | 259 | 478 | Literary | | 8 | Python | 67 | 23 | 83 | 543 | Holiday | | 9 | PHP | 673 | 48 | 625 | 52 | Profession | +-----+------------+--------+--------+--------+------+------------+ 9 rows in set (0.01 sec) mysql> /* Use two ORDER BY fields to display the design names within each catego ry in a sorted order. mysql> */ mysql> SELECT name, category FROM sales

   -> ORDER BY category, name;

/

Drop table sales;

CREATE TABLE sales(

   num MEDIUMINT NOT NULL AUTO_INCREMENT,
   name CHAR(20),
   winter INT,
   spring INT,
   summer INT,
   fall INT,
   category CHAR(13),
   primary key(num)

)type=MyISAM; insert into sales value(1, "Java", 1067 , 200, 150, 267,"Holiday"); insert into sales value(2, "C",970,770,531,486,"Profession"); insert into sales value(3, "JavaScript",53,13,21,856,"Literary"); insert into sales value(4, "SQL",782,357,168,250,"Profession"); insert into sales value(5, "Oracle",589,795,367,284,"Holiday"); insert into sales value(6, "MySQL",953,582,336,489,"Literary"); insert into sales value(7, "Cplus",752,657,259,478,"Literary"); insert into sales value(8, "Python",67,23,83,543,"Holiday"); insert into sales value(9, "PHP",673,48,625,52,"Profession"); select * from sales; /* Use two ORDER BY fields to display the design names within each category in a sorted order.

*/

SELECT name, category FROM sales ORDER BY category, name;

      </source>

SQL/MySQL/Select Clause/Sort Order

Содержание

Another decendingly

Default sort order is ascending

Indicate of ascend

Narrow down data with condition and order it

Order BY and Limit

Order decending

Order result wiht ORDER

Order row in select clause

Order two columns with different orders

Simple ORDER by

Sort columns in different directions

Sorting Data

Sorting Rows

Sort on multiple columns

To sort in reverse (descending) order

Use ORDER BY to list

Use order by to sort the result

Use two ORDER BY fields

Навигация

Персональные инструменты

Пространства имён

Варианты

Просмотры

Ещё

Поиск

Разделы

Навигация

Инструменты