LIMIT付きのSELECT文の行数

参考URL。
http://www.postgresql.org/docs/8.4/static/tutorial-window.html
http://www.postgresql.jp/document/8.4/html/tutorial-window.html
http://hibari.2ch.net/test/read.cgi/db/1294641578/401-497

PostgreSQL。

$ eix postgresql-server -cI
[I] dev-db/postgresql-server (9.0.4-r1(9.0)@2011年07月04日): PostgreSQL server

user=# CREATE TABLE test001 (id SERIAL, data TEXT);
NOTICE:  CREATE TABLEはシリアル列"test001.id"用に暗黙的なシーケンス"test001_id_seq"を作成します。
CREATE TABLE
user=# INSERT INTO test001 (data) VALUES ('aaa');
INSERT 0 1
user=# INSERT INTO test001 (data) VALUES ('bbb');
INSERT 0 1
user=# INSERT INTO test001 (data) VALUES ('ccc');
INSERT 0 1
user=# SELECT * FROM test001 ORDER BY id DESC;
 id | data
----+------
  3 | ccc
  2 | bbb
  1 | aaa
(3 行)

user=# SELECT COUNT(*) AS cntall FROM test001;
 cntall
--------
      3
(1 行)

user=# SELECT *,COUNT(*) OVER() AS cntall FROM test001 ORDER BY id DESC LIMIT 1;
 id | data | cntall
----+------+--------
  3 | ccc  |      3
(1 行)

user=#

MySQL。

$ eix -e dev-db/mysql -cI
[I] dev-db/mysql (5.1.56@2011年05月09日): A fast, multi-threaded, multi-user SQL database server.

mysql> CREATE TABLE test001 (id SERIAL, data TEXT);
Query OK, 0 rows affected (0.23 sec)

mysql> INSERT INTO test001 (data) VALUES ('aaa');
Query OK, 1 row affected (0.93 sec)

mysql> INSERT INTO test001 (data) VALUES ('bbb');
Query OK, 1 row affected (0.15 sec)

mysql> INSERT INTO test001 (data) VALUES ('ccc');
Query OK, 1 row affected (0.14 sec)

mysql> SELECT * FROM test001 ORDER BY id DESC;
+----+------+
| id | data |
+----+------+
|  3 | ccc  |
|  2 | bbb  |
|  1 | aaa  |
+----+------+
3 rows in set (0.00 sec)

mysql> SELECT COUNT(*) AS cntall FROM test001;
+--------+
| cntall |
+--------+
|      3 |
+--------+
1 row in set (0.00 sec)

mysql> SELECT SQL_CALC_FOUND_ROWS * FROM test001 ORDER BY id DESC LIMIT 1;
+----+------+
| id | data |
+----+------+
|  3 | ccc  |
+----+------+
1 row in set (0.00 sec)

mysql> SELECT FOUND_ROWS() AS cntall;
+--------+
| cntall |
+--------+
|      3 |
+--------+
1 row in set (0.00 sec)

mysql>