"Ghetalion" <[EMAIL PROTECTED]> wrote:
> I figure since there are various ways to sort information in SQL, there
must
> be some way to sort the most frequent bit of information. Example:
>
> SELECT term FROM table ORDER BY mostfrequent(term)
>
> ID TERM
> 1 a
> 2 a
> 3 f
> 4 z
> 5 a
> 6 i
> 7 i
> 8 z
> 9 z
> 10 a
>
> It would return:
>
> a
> a
> a
> a
> z
> z
> z
> i
> i
> f
>
> It there a fuction like that availble built into MySQL?
Not exactly. Look at the online manual for "aggregate" functions. The
query below will calculate the totals.
SELECT term, COUNT(term) AS occurrences
FROM table
GROUP BY term
ORDER BY occurrences DESC
However, you appear to want to show *all* of the records, but you want to
show them in the order determined by the query above. I don't think this is
possible in one step using MySQL since the way I know to do it would require
a subselect, which is something MySQL doesn't yet support. A solution is to
save the output of the above SQL statement into a table (or a temporary
table using CREATE TEMPORARY TABLE) and then use SELECT LEFT JOIN to get
what you want.
--
Steve Werby
President, Befriend Internet Services LLC
http://www.befriend.com/
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
