[SOLVED] ORDER BY most popular

[Canman2005]

Hi all

 

I have a table called `members` which looks like

 

id

user_id

type

 

I want to do a simple

 

SELECT `user_id` FROM `members` WHERE `type` = 'J'

 

My question is.

 

How can I get the QUERY do the ORDER BY most popular, using the `user_id` and LIMIT it by 3

 

So if there are

 

9 rows with `user_id` value set as 3

28 rows with `user_id` value set as 7

2 rows with `user_id` value set as 13

55 rows with `user_id` value set as 18

 

then the QUERY would return `user_id` in the following order

 

`user_id`

18 (as it has 55 rows)

7 (as it has 28 rows)

3 (as it has 9 rows)

 

Any help would be great, been hunting for an answer for the last 4 hours with no luck

 

thanks very much in advance

 

dave

[trq]

SELECT DISTINCT user_id, COUNT(user_id) as total FROM members WHERE type = 'J' ORDER BY total DESC;

[Canman2005]

That just seems to return one row, any ideas?

[Canman2005]

any ideas anyone? can only seem to get 1 row returned

[alphanumetrix]

Hey,

 

I think we are working on the same (or at least a similar) project. Mark asked me to help you out.

 

I am not sure how to do this with MySQL, but there are a couple ways of doing this using PHP. How many users are you working with?

[corbin]

That just seems to return one row, any ideas?

 

 

Hrmm, I would've thought that would work, but an alternative could be:

 

 

SELECT user_id, COUNT(user_id) AS count FROM members WHERE type = 'j' GROUP BY user_id;

[Canman2005]

SELECT user_id, COUNT(user_id) AS count FROM members WHERE type = 'j' GROUP BY user_id;

 

worked great thanks