CODEWITHSUNDEEP
mysqlview
Omar Abid
Omar Abid

Reputation: 15976

Limit the number of rows in a LENGTH statement

I have the following mysql code in a view

SELECT 
threadid,
SUM(LENGTH(pagetext)-LENGTH(REPLACE(pagetext, ' ', ''))+1)

FROM post
GROUP by threadid

This count the number of words in each column with the same "threadid", and return the results grouped by the threadid. It works fine, but I want to limit the words counted only for the first 5 rows in each column. How can this be achieved?

Table Structure

+-----------------+-------------+
| threadID        | pagetext    |
+-----------------+-------------+
| 3               | some text   |
| 3               | other text  |
| 5               | text        |
+-----------------+-------------+

The view returns

+-----------------+-------------+
| threadID        | count       |
+-----------------+-------------+
| 3               | 4           |
| 5               | 1           |
+-----------------+-------------+

I'm looking to limit the count number to only n rows for each threadID. For example, if I limit the count to only 1 row, the threadID "3" will have a "count" of 2.

Upvotes: 1

Views: 74

Answers (1)

rabudde
rabudde

Reputation: 7722

SELECT
    threadid,
    SUM(LENGTH(pagetext)-LENGTH(REPLACE(pagetext, ' ', ''))+1) AS len
FROM (
    SELECT
        p1.threadid,
        p2.pagetext,
        COUNT(*) AS num
    FROM post AS p1
    JOIN post AS p2 ON p1.threadid=p2.threadid AND p1.postid <= p2.postid
    GROUP BY p1.threadid, p2.pagetext
    HAVING num<=5
) a
GROUP BY threadid

EDIT: Best way could be, that there is an auto-incremented column (I'll name it postid), which helps us to get the first five posts for each thread.

Upvotes: 1

Related Questions