Tricky SQL Select statement

Hey guys. Got a problem for anyone with decent mySQL skills.

I have a database containing my server log files. A sample of the rows would be:

+--------------+------------+-----------------+--------+

| ip		   | time	   | resource		| status |

+--------------+------------+-----------------+--------+

| 195.93.21.71 | 1112868867 | /about/		 |	200 |

| 195.93.21.4  | 1112868869 | /css/normal.css |	200 |

| 195.93.21.2  | 1112868872 | /css/print.css  |	200 |

+--------------+------------+-----------------+--------+

Some of the IPs and resources will be the duplicates in the full table. I can select out the number of unique IPs and resources with the following query:

SELECT COUNT(DISTINCT ip) FROM stats

But what I now want to do is find which resource has been requested the most. By this I mean extract the distinct resources (one of each) ordered by number of times it occurs. I've been trying variations on the following with no luck:

SELECT resource from stats ORDER BY COUNT(DISTINCT resource)

Can anyone tell me if this is even possible and/or point me in the right direction to go?

Note: Even pointing me to a decent mySQL forum would be helpful.

Thanks.

#272185

try this:

SELECT resource, COUNT(resource) FROM stats GROUP BY(resource) ORDER BY 2 DESC

#272242

Thanks Vorlon. I was just coming back to say that I'd cracked it and read your post. I'd got almost exactly the same query!

Thanks anyway.

#272244