diff options
Diffstat (limited to 'mysql-test/r/subselect.result')
| -rw-r--r-- | mysql-test/r/subselect.result | 45 |
1 files changed, 45 insertions, 0 deletions
diff --git a/mysql-test/r/subselect.result b/mysql-test/r/subselect.result index 11dc68fe99b..13f653b2f07 100644 --- a/mysql-test/r/subselect.result +++ b/mysql-test/r/subselect.result @@ -3238,3 +3238,48 @@ i 10000000000000000000 DROP TABLE t1; DROP TABLE t2; +CREATE TABLE t1 ( +id bigint(20) unsigned NOT NULL auto_increment, +name varchar(255) NOT NULL, +PRIMARY KEY (id) +); +INSERT INTO t1 VALUES +(1, 'Balazs'), (2, 'Joe'), (3, 'Frank'); +CREATE TABLE t2 ( +id bigint(20) unsigned NOT NULL auto_increment, +mid bigint(20) unsigned NOT NULL, +date date NOT NULL, +PRIMARY KEY (id) +); +INSERT INTO t2 VALUES +(1, 1, '2006-03-30'), (2, 2, '2006-04-06'), (3, 3, '2006-04-13'), +(4, 2, '2006-04-20'), (5, 1, '2006-05-01'); +SELECT *, +(SELECT date FROM t2 WHERE mid = t1.id +ORDER BY date DESC LIMIT 0, 1) AS date_last, +(SELECT date FROM t2 WHERE mid = t1.id +ORDER BY date DESC LIMIT 3, 1) AS date_next_to_last +FROM t1; +id name date_last date_next_to_last +1 Balazs 2006-05-01 NULL +2 Joe 2006-04-20 NULL +3 Frank 2006-04-13 NULL +SELECT *, +(SELECT COUNT(*) FROM t2 WHERE mid = t1.id +ORDER BY date DESC LIMIT 1, 1) AS date_count +FROM t1; +id name date_count +1 Balazs NULL +2 Joe NULL +3 Frank NULL +SELECT *, +(SELECT date FROM t2 WHERE mid = t1.id +ORDER BY date DESC LIMIT 0, 1) AS date_last, +(SELECT date FROM t2 WHERE mid = t1.id +ORDER BY date DESC LIMIT 1, 1) AS date_next_to_last +FROM t1; +id name date_last date_next_to_last +1 Balazs 2006-05-01 2006-03-30 +2 Joe 2006-04-20 2006-04-06 +3 Frank 2006-04-13 NULL +DROP TABLE t1,t2; |