CODEWITHSUNDEEP
mysqlsql
Stuck
Stuck

Reputation: 12302

sql query + limit + count overall results in one query?

SELECT topics.*
FROM topics topics
    JOIN rh_topictags tt ON tt.topic_id = topics.topic_id
    JOIN rh_topictags_tag t ON tt.tag_id = t.id
    JOIN forums f ON f.forum_id = topics.forum_id
WHERE LOWER(t.tag) IN ('super', 'fun')
    AND f.rh_topictags_enabled = 1
    AND topics.forum_id IN (1, 2, 3, 4)
    AND topics.topic_visibility = 1
GROUP BY topics.topic_id
HAVING count(t.id) = 2
ORDER BY topics.topic_last_post_time DESC
LIMIT 0, 10

How can I get the count of the overall found rows (without the limit)?

I can not use SELECT FOUND_ROWS() as this query must run under several DBMS (mysql, mssql, oracle, postgres, sqlite, sqlite3).

Unfortunatly this does not work:

SELECT topics.*, COUNT(topics.*) AS result_count
FROM topics topics
    JOIN rh_topictags tt ON tt.topic_id = topics.topic_id
    JOIN rh_topictags_tag t ON tt.tag_id = t.id
    JOIN forums f ON f.forum_id = topics.forum_id
WHERE LOWER(t.tag) IN ('super', 'fun')
    AND f.rh_topictags_enabled = 1
    AND topics.forum_id IN (1, 2, 3, 4)
    AND topics.topic_visibility = 1
GROUP BY topics.topic_id
HAVING count(t.id) = 2
ORDER BY topics.topic_last_post_time DESC
LIMIT 0, 10

Upvotes: 0

Views: 60

Answers (1)

Milen
Milen

Reputation: 8877

What about this:

Select * 
from
( 
    SELECT topics.*, count(*) as result_count
    FROM topics topics
        JOIN rh_topictags tt ON tt.topic_id = topics.topic_id
        JOIN rh_topictags_tag t ON tt.tag_id = t.id
        JOIN forums f ON f.forum_id = topics.forum_id
    WHERE LOWER(t.tag) IN ('super', 'fun')
        AND f.rh_topictags_enabled = 1
        AND topics.forum_id IN (1, 2, 3, 4)
        AND topics.topic_visibility = 1
    GROUP BY topics.topic_id
    HAVING count(t.id) = 2
) a
ORDER BY a.topic_last_post_time DESC
LIMIT 0, 10

Upvotes: 0

Related Questions