Ordering Results

[kieti]

Is there any way I could use ORDER BY to sort results by the frequency of a sub-string within a mySQL text field?

[daus95]

you should give more explaination on your problem, like an example. may be i can help you.

[Anon]

are you grouping the results and then wanting to sort by the number of occurences of the group?

$sql =
"SELECT *, count(state) as num_state
FROM database
WHERE state = '$state'
GROUP BY state
ORDER BY num_state DESC";

I think this will work? Haven't tested it.

[Anon]

Given a text field defined as
'text1 TEXT' and a substring to search defined as 'keyword', I want to query the database and get all records whose 'text1' field has at least 1 appearance of the substring 'keyword' (using WHERE LIKE '%keyword%')

Further, I want to order my results according to the number of times the keyword has appeared in the field - such that a record whose text1 field has 4 instanses of the substring 'keyword' appears before a record whose text1 field has only one instance of the substring.

Thanks in advance!