mysql query herlp (frequency)

I have a table with two fields. One field is a number 0 or 1. The other field is an ID.

I would like to select the IDs ordered by the occurrences of the field with number 1.

Say if I have:

B 1
B 1
B 0
A 1
A 0

The query should order it B than A because B has the most occurrences of 1.

How would I do that? I'm guessing that I need to create some temporary variable.

I'm not sure you can do it with an SQL query only. Since it is not a traditional sort algorithm, I think you should implement part of it with PHP.

Something like this is what you need?

SELECT
	`id`, `type`, (SELECT COUNT(*) FROM `table` WHERE `type`='1' AND `id`=t.`id`) as nr_occurence
FROM `table` t
ORDER BY nr_occurence DESC

Where:

id char(1) NOT NULL default '',
type enum('0','1') NOT NULL default '0'

SELECT *, COUNT(field2) AS theCOUNT FROM table GROUP BY field2 ORDER BY theCount DESC

bogu wrote:

Something like this is what you need?

SELECT
	`id`, `type`, (SELECT COUNT(*) FROM `table` WHERE `type`='1' AND `id`=t.`id`) as nr_occurence
FROM `table` t
ORDER BY nr_occurence DESC

Where:

Thanks, I think this works.