mysql count selected field

Question

I have a query that selects the nth results (in this case the 10th) I just want to use the reply_chunk_id of that nth result and count others like it to see how many there are in total with the same id. Does anyone know how to do this in one query?

SELECT reply_chunk_id,message 
FROM (
    SELECT * 
    FROM messages
    ORDER BY timestamp ASC
    LIMIT 10
) AS tbl WHERE topic_id=?
ORDER BY timestamp DESC
LIMIT 1

Answer 1

use LIMIT 1 OFFSET 9 maybe. Not sure this is built in MySQL.

Answer 2

You can select the 10th row by

SELECT reply_chunk_id FROM messages ORDER BY timestamp ASC LIMIT 9,1

so

SELECT COUNT(*) FROM messages
   WHERE reply_chunk_id = (SELECT reply_chunk_id FROM messages
                              ORDER BY timestamp ASC LIMIT 9,1)
     AND topic_id = ?

Answer 3

SELECT COUNT(1)
FROM messages
WHERE reply_chunk_id =
(SELECT MIN(reply_chunk_id)
 FROM messages
 WHERE timestamp =
    (SELECT MAX(timestamp)
     FROM   (SELECT timestamp
             FROM   messages
             ORDER BY timestamp ASC
             LIMIT 10)))