Limits and order : Limits « Select Clause « SQL / MySQL

Limits and order

  
/*
mysql> Drop table Item;
Query OK, 0 rows affected (0.01 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.05 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:50 |
|  2 | Most      |      10 |       5 |        2 | Classical  | Opera    | 2005-10-09 09:19:50 |
|  3 | Jazz      |      17 |       4 |        3 | Popular    | Jazz     | 2005-10-09 09:19:50 |
|  4 | Class     |       9 |       4 |        4 | Classical  | Dance    | 2005-10-09 09:19:50 |
|  5 | Violin    |      24 |       2 |        5 | Classical  | General  | 2005-10-09 09:19:50 |
|  6 | Cha Cha   |      16 |       6 |        6 | Classical  | Vocal    | 2005-10-09 09:19:50 |
|  7 | Blues     |       2 |      25 |        7 | Popular    | Blues    | 2005-10-09 09:19:50 |
|  8 | Pure      |      32 |       3 |       18 | Popular    | Jazz     | 2005-10-09 09:19:50 |
|  9 | Mud       |      12 |      15 |       19 | Popular    | Country  | 2005-10-09 09:19:50 |
| 10 | The       |       5 |      20 |       11 | Popular    | New Age  | 2005-10-09 09:19:50 |
| 11 | Embrace   |      24 |      11 |       12 | Popular    | New Age  | 2005-10-09 09:19:50 |
| 12 | Magic     |      42 |      17 |       13 | Classical  | General  | 2005-10-09 09:19:50 |
| 13 | Lake      |      25 |      44 |       24 | Classical  | Dance    | 2005-10-09 09:19:50 |
| 14 | LaLala    |      20 |      10 |        5 | Classical  | Opera    | 2005-10-09 09:19:50 |
| 15 | Soul      |      15 |      30 |       16 | Popular    | Blues    | 2005-10-09 09:19:50 |
| 16 | Stages    |      42 |       0 |        7 | Popular    | Blues    | 2005-10-09 09:19:50 |
| 17 | Six       |      16 |       8 |        6 | Classical  | General  | 2005-10-09 09:19:50 |
+----+-----------+---------+---------+----------+------------+----------+---------------------+
17 rows in set (0.00 sec)

mysql> SELECT ID, Name, InStock
    -> FROM Item
    -> WHERE Department='Classical'
    -> ORDER BY ID DESC
    -> LIMIT 3,4;
+----+---------+---------+
| ID | Name    | InStock |
+----+---------+---------+
| 12 | Magic   |      42 |
|  6 | Cha Cha |      16 |
|  5 | Violin  |      24 |
|  4 | Class   |       9 |
+----+---------+---------+
4 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 ID, Name, InStock
FROM Item
WHERE Department='Classical'
ORDER BY ID DESC
LIMIT 3,4;

Related examples in the same category

1.	Limit the query results: Get 3 Most Expensive Products
2.	Use LIMIT in SELECT
3.	Use LIMIT
4.	Another ORDER BY and limit
5.	Simple LIMIT
6.	Retrieving the Top Five Students
7.	ORDER and limit
8.	Scrolling through the entire product table four records at a time
9.	Determining the Number of Records Suppressed by LIMIT (SQL_CALC_FOUND_ROWS, FOUND_ROWS)
10.	Limiting a Selection Using LIMIT
11.	Use ORDER BY to sort the result set. Then you can use LIMIT to find smallest and largest values.
12.	Order the rows with the largest SUM( ) values first and use LIMIT to select the first record
13.	A DELETE statement can be qualified by using an ORDER BY and a LIMIT clause
14.	The LIMIT clause takes two arguments, as the following syntax shows: LIMIT [,]
15.	SELECT statement includes a LIMIT clause that specifies an offset value of 3 and a row count value of 4
16.	WHERE clause can include additional logical operators and expressions that further limit the results returned
17.	Limit to first three columns
18.	LIMIT 5 OFFSET 3
19.	Delete with order by and limit clause
20.	Pulling a Section from the Middle of a Result Set
21.	Tell MySQL what offset to use, from which result to start limiting.
22.	The default offset is 0, so by specifying an offset of 1, you're taking the second record.
23.	Selecting Records from the Beginning or End of a Result Set
24.	Processing the First or Last n Records
25.	Limiting the Number of Results
26.	But LIMIT allows you to return only that record