2nd UPDATE:
Deriving @OMG Ponies' solution for the updated question:
SELECT t.name,
MAX(CASE WHEN x.rank = 1 THEN x.timeping END) AS timeping1,
MAX(CASE WHEN x.rank = 2 THEN x.timeping END) AS timeping2
FROM terminals t
LEFT JOIN (
SELECT tp.timeping, tp.terminalsId,
IF(@curId <> terminalsId, @rownum := 0, 0),
@rownum := @rownum + 1 AS rank,
@curId := terminalsId
FROM terminalPings tp
JOIN (SELECT @rownum := 0, @curId := 0) r
ORDER BY tp.terminalsId, tp.timeping DESC
) x ON (x.terminalsId = t.id AND x.rank <= 2)
GROUP BY t.name;
New test case:
CREATE TABLE terminals (id int, name varchar(20));
INSERT INTO terminals VALUES (1, 'Terminal 1');
INSERT INTO terminals VALUES (2, 'Terminal 2');
INSERT INTO terminals VALUES (3, 'Terminal 3');
CREATE TABLE terminalPings (terminalsId int, timePing int);
INSERT INTO terminalPings VALUES (1, 5);
INSERT INTO terminalPings VALUES (1, 8);
INSERT INTO terminalPings VALUES (1, 4);
INSERT INTO terminalPings VALUES (2, 6);
INSERT INTO terminalPings VALUES (2, 5);
INSERT INTO terminalPings VALUES (3, 4);
INSERT INTO terminalPings VALUES (3, 7);
INSERT INTO terminalPings VALUES (3, 2);
New result:
+------------+-----------+-----------+
| name | timeping1 | timeping2 |
+------------+-----------+-----------+
| Terminal 1 | 8 | 5 |
| Terminal 2 | 6 | 5 |
| Terminal 3 | 7 | 4 |
+------------+-----------+-----------+
3 rows in set (0.00 sec)
EXPLAIN output (MySQL 5.1.45)
+----+-------------+------------+--------+---------------+------+---------+------+------+---------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+------------+--------+---------------+------+---------+------+------+---------------------------------+
| 1 | PRIMARY | t | ALL | NULL | NULL | NULL | NULL | 3 | Using temporary; Using filesort |
| 1 | PRIMARY | <derived2> | ALL | NULL | NULL | NULL | NULL | 8 | |
| 2 | DERIVED | <derived3> | system | NULL | NULL | NULL | NULL | 1 | Using filesort |
| 2 | DERIVED | tp | ALL | NULL | NULL | NULL | NULL | 8 | |
| 3 | DERIVED | NULL | NULL | NULL | NULL | NULL | NULL | NULL | No tables used |
+----+-------------+------------+--------+---------------+------+---------+------+------+---------------------------------+
5 rows in set (0.00 sec)
UPDATE: @OMG Ponies' solution is faster than the one I suggested earlier (see the comments to both answers for reasons). I'm leaving the answer here for reference (this answer still returns a correct result, just much slower when you have many rows in the outer query: in the terminals table).
I would still stuggest using two separate queries. While SQL is a very expressive language, there's no need to do everything in one query... just like there's no need to do a task in a single line of code in other programming languages! The fact that MySQL made the subqueries in the solution below uncachable is one form of potential problems that you may face when using complicated queries (which may easily be broken down into two or more very simply queries).
Previous Answer:
You may want to use two separate queries instead. But just for the challenge, you may want to try the following:
SELECT name,
(
SELECT timePing FROM
(
SELECT timePing, @rowid := @rowid + 1 rownum
FROM TerminalPings, (SELECT @rowid := 0) rn
ORDER BY timePing DESC
LIMIT 1
) t
WHERE t.rownum = 1
) AS timePing_1,
(
SELECT timePing FROM
(
SELECT timePing, @rowid := @rowid + 1 rownum
FROM TerminalPings, (SELECT @rowid := 0) rn
ORDER BY timePing DESC
LIMIT 2
) t
WHERE t.rownum = 2
) AS timePing_2
FROM Terminals;
Test case:
CREATE TABLE terminals (name varchar(20));
INSERT INTO terminals VALUES ('Terminal 1');
INSERT INTO terminals VALUES ('Terminal 2');
INSERT INTO terminals VALUES ('Terminal 3');
CREATE TABLE terminalPings (timePing int);
INSERT INTO terminalPings VALUES (1);
INSERT INTO terminalPings VALUES (2);
INSERT INTO terminalPings VALUES (3);
INSERT INTO terminalPings VALUES (4);
Result:
+------------+------------+------------+
| name | timePing_1 | timePing_2 |
+------------+------------+------------+
| Terminal 1 | 4 | 3 |
| Terminal 2 | 4 | 3 |
| Terminal 3 | 4 | 3 |
+------------+------------+------------+
3 rows in set (0.01 sec)
EXPLAIN output (MySQL 5.1.45) (see the comments for @OMG Ponies' answer):
+----+----------------------+---------------+--------+---------------+------+---------+------+------+----------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+----------------------+---------------+--------+---------------+------+---------+------+------+----------------+
| 1 | PRIMARY | Terminals | ALL | NULL | NULL | NULL | NULL | 3 | |
| 5 | UNCACHEABLE SUBQUERY | <derived6> | ALL | NULL | NULL | NULL | NULL | 2 | Using where |
| 6 | DERIVED | <derived7> | system | NULL | NULL | NULL | NULL | 1 | Using filesort |
| 6 | DERIVED | TerminalPings | ALL | NULL | NULL | NULL | NULL | 4 | |
| 7 | DERIVED | NULL | NULL | NULL | NULL | NULL | NULL | NULL | No tables used |
| 2 | UNCACHEABLE SUBQUERY | <derived3> | system | NULL | NULL | NULL | NULL | 1 | |
| 3 | DERIVED | <derived4> | system | NULL | NULL | NULL | NULL | 1 | Using filesort |
| 3 | DERIVED | TerminalPings | ALL | NULL | NULL | NULL | NULL | 4 | |
| 4 | DERIVED | NULL | NULL | NULL | NULL | NULL | NULL | NULL | No tables used |
+----+----------------------+---------------+--------+---------------+------+---------+------+------+----------------+