Processing the First or Last n Records

      
mysql>
mysql> CREATE TABLE messages (
    ->   msgID int(20) NOT NULL auto_increment PRIMARY KEY,
    ->   forumID int(11),
    ->   rootID int(11),
    ->   parentID int(11),
    ->   userID int(11),
    ->   subject varchar(80),
    ->   msgText text,
    ->   ts timestamp
    -> );
Query OK, 0 rows affected (0.00 sec)

mysql>
mysql> INSERT INTO messages VALUES (60,1002,60,NULL,375,'cgi','','2001-03-27 08:35:28'),
    ->                             (62,3,62,NULL,162,'Message','','2001-03-29 16:07:29');
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql>
mysql>
mysql>
mysql>
mysql> SELECT LEFT(subject, 20) AS subj, forumID, ts
    -> FROM messages ORDER BY ts DESC LIMIT 5;
+---------+---------+---------------------+
| subj    | forumID | ts                  |
+---------+---------+---------------------+
| Message |       3 | 2001-03-29 16:07:29 |
| cgi     |    1002 | 2001-03-27 08:35:28 |
+---------+---------+---------------------+
2 rows in set (0.00 sec)

mysql>
mysql>
mysql> SELECT LEFT(subject, 20) AS subj, forumID, ts FROM messages
    -> WHERE rootID=msgID AND forumID=1006
    -> ORDER BY ts DESC LIMIT 5;
Empty set (0.00 sec)

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

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

Processing the First or Last n Records : Limits « Select Clause « SQL / MySQL

Related examples in the same category