Order by with 'limit' clause : ORDER BY « Select Query « MySQL Tutorial






  1. MySQL Tutorial
  2. Select Query
  3. ORDER BY
mysql>
mysql> CREATE TABLE Topic(
    ->    TopicID     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.03 sec)

mysql>
mysql>
mysql> INSERT INTO Topic (Name,          InStock, OnOrder, Reserved, Department,   Category) VALUES
    ->                   ('Java',          10,      5,       3,        'Popular',    'Rock'),
    ->                   ('JavaScript',    10,      5,       3,        'Classical',  'Opera'),
    ->                   ('C Sharp',       17,      4,       1,        'Popular',    'Jazz'),
    ->                   ('C',             9,       4,       2,        'Classical',  'Dance'),
    ->                   ('C++',           24,      2,       5,        'Classical',  'General'),
    ->                   ('Perl',          16,      6,       8,        'Classical',  'Vocal'),
    ->                   ('Python',        2,       25,      6,        'Popular',    'Blues'),
    ->                   ('Php',           32,      3,       10,       'Popular',    'Jazz'),
    ->                   ('ASP.net',       12,      15,      13,       'Popular',    'Country'),
    ->                   ('VB.net',        5,       20,      10,       'Popular',    'New Age'),
    ->                   ('VC.net',        24,      11,      14,       'Popular',    'New Age'),
    ->                   ('UML',           42,      17,      17,       'Classical',  'General'),
    ->                   ('www.java2s.com',25,      44,      28,       'Classical',  'Dance'),
    ->                   ('Oracle',        32,      15,      12,       'Classical',  'General'),
    ->                   ('Pl/SQL',        20,      10,      5,        'Classical',  'Opera'),
    ->                   ('Sql Server',    23,      12,      8,        'Classical',  'General');
Query OK, 16 rows affected (0.02 sec)
Records: 16  Duplicates: 0  Warnings: 0

mysql>
mysql> select * from Topic;
+---------+----------------+---------+---------+----------+------------+----------+---------------------+
| TopicID | Name           | InStock | OnOrder | Reserved | Department | Category | RowUpdate           |
+---------+----------------+---------+---------+----------+------------+----------+---------------------+
|       1 | Java           |      10 |       5 |        3 | Popular    | Rock     | 2007-07-23 19:09:45 |
|       2 | JavaScript     |      10 |       5 |        3 | Classical  | Opera    | 2007-07-23 19:09:45 |
|       3 | C Sharp        |      17 |       4 |        1 | Popular    | Jazz     | 2007-07-23 19:09:45 |
|       4 | C              |       9 |       4 |        2 | Classical  | Dance    | 2007-07-23 19:09:45 |
|       5 | C++            |      24 |       2 |        5 | Classical  | General  | 2007-07-23 19:09:45 |
|       6 | Perl           |      16 |       6 |        8 | Classical  | Vocal    | 2007-07-23 19:09:45 |
|       7 | Python         |       2 |      25 |        6 | Popular    | Blues    | 2007-07-23 19:09:45 |
|       8 | Php            |      32 |       3 |       10 | Popular    | Jazz     | 2007-07-23 19:09:45 |
|       9 | ASP.net        |      12 |      15 |       13 | Popular    | Country  | 2007-07-23 19:09:45 |
|      10 | VB.net         |       5 |      20 |       10 | Popular    | New Age  | 2007-07-23 19:09:45 |
|      11 | VC.net         |      24 |      11 |       14 | Popular    | New Age  | 2007-07-23 19:09:45 |
|      12 | UML            |      42 |      17 |       17 | Classical  | General  | 2007-07-23 19:09:45 |
|      13 | www.java2s.com |      25 |      44 |       28 | Classical  | Dance    | 2007-07-23 19:09:45 |
|      14 | Oracle         |      32 |      15 |       12 | Classical  | General  | 2007-07-23 19:09:45 |
|      15 | Pl/SQL         |      20 |      10 |        5 | Classical  | Opera    | 2007-07-23 19:09:45 |
|      16 | Sql Server     |      23 |      12 |        8 | Classical  | General  | 2007-07-23 19:09:45 |
+---------+----------------+---------+---------+----------+------------+----------+---------------------+
16 rows in set (0.00 sec)

mysql>
mysql> SELECT TopicID, Name, InStock
    -> FROM Topic
    -> WHERE Department='Classical'
    -> ORDER BY TopicID DESC
    -> LIMIT 3,4;
+---------+----------------+---------+
| TopicID | Name           | InStock |
+---------+----------------+---------+
|      13 | www.java2s.com |      25 |
|      12 | UML            |      42 |
|       6 | Perl           |      16 |
|       5 | C++            |      24 |
+---------+----------------+---------+
4 rows in set (0.00 sec)

mysql>
mysql> drop table Topic;
Query OK, 0 rows affected (0.01 sec)








2.4.ORDER BY
2.4.1.ORDER BY controls the sequence of results
2.4.2.Sort the results in reverse by appending DESC to the condition
2.4.3.To sort in reverse (descending) order, add the DESC keyword to the name of the column
2.4.4.DESC keyword applies only to the column name immediately preceding it
2.4.5.We can use two or more ORDER BY conditions
2.4.6.Sort by date
2.4.7.Order by with 'limit' clause
2.4.8.Order first 4 rows
2.4.9.You can retrieve rows in random order
2.4.10.On character type columns, sorting is normally performed in a case-insensitive fashion
2.4.11.To use a case-sensitive sort for a column by using BINARY
2.4.12.Using both DESC and ASC in order clause