CODEWITHSUNDEEP
sqlmysql
Scarface
Scarface

Reputation: 3913

mysql count selected field

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

Upvotes: 0

Views: 164

Answers (3)

Andrew
Andrew

Reputation: 14447

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)))

Upvotes: 0

kennytm
kennytm

Reputation: 523304

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 = ?

Upvotes: 3

Benoit
Benoit

Reputation: 79185

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

Upvotes: 0

Related Questions