MySQL: Selecting rows ordered by word count

How can I order my query by word cou开发者_开发问答nt? Is it possible?

I have some rows in table, with text fields. I want to order them by word count of these text fields.

Second problem is, that I need to select only these rows, which have for example minimum 10 words, or maximum 20.

Well, this will not perform very well since string calculations need to be performed for all rows:

You can count number of words in a MySQL column like so: SELECT SUM( LENGTH(name) - LENGTH(REPLACE(name, ' ', ''))+1) FROM table (provided that words are defined as "whatever-delimited-by-a-whitespace")

Now, add this to your query:

SELECT
    <fields>
FROM
    <table>
WHERE
    <condition>
ORDER BY SUM(LENGTH(<fieldWithWords>) - LENGTH(REPLACE(<fieldWithWords>, ' ', '')) + 1)

Or, add it to the condition:

SELECT
    <fields>
FROM
    <table>
WHERE
    SUM(LENGTH(<fieldWithWords>) - LENGTH(REPLACE(<fieldWithWords>, ' ', '')) + 1) BETWEEN 10 AND 20
ORDER BY <something>

Maybe something like this:

SELECT Field1, SUM( LENGTH(Field2) - LENGTH(REPLACE(Field2, ' ', ''))+1)
 AS cnt
FROM tablename
GROUP BY Field1
ORDER BY cnt

Field2 is the string field in which you'd like to count words.