add up like rows and order by

[cdoyle]

Hi,

this seems like it would be easy,  but I can't seem to get it to work.

 

Here is what I want to do,  I have a table that has the following structure.

 

ID

player_id

enemy_id

 

I want to have to display the top 5 players who have the most enemies

 

so I need to group the 'enemy_id' field and then sort it by the highest count.

 

so if I have data like this

 

row 1 | enemy_id=121   (ME)

row 2 | enemy_id=134   (Someone else)

row 3 | enemy_id=121   (ME)

 

the result would look something like this on my page

 

ME = 2

someone else 1
 

How can I do this?

 

Thanks

Chris

[Barand]

try

SELECT player_id, COUNT(DISTINCT enemy_id) as enemies
FROM tablename
GROUP BY player_id
ORDER BY enemies DESC

[cdoyle]

Thanks for your help.

I tried this but it's not quite working,  if I wanted to display the count I would use 'enemies' correct?

 

Right now,  all entries are showing '1' as the total enemy count.

 

I did write it a little differently than you though.

 

I basically want to see the players who have been marked as a 'enemy' the most

So I did this,  I think yours would show who marked the most people as an enemy?  but I could be wrong.

 

SELECT enemy_id, COUNT(DISTINCT enemy_id) as enemies
FROM tablename
GROUP BY enemy_id
ORDER BY enemies DESC

 

but everything is showing 1.

[Barand]

I want to have to display the top 5 players who have the most enemies

 

 

I basically want to see the players who have been marked as a 'enemy' the most

 

Which is it?

[cdoyle]

Which is it?

I'm sorry,  I didn't describe it quite right the first time.  I should have said

 

I want to display the players who have been marked an 'enemy' the most (top 5).

[Barand]

Just swap the fields

SELECT enemy_id, COUNT(DISTINCT player_id) as enemies
FROM tablename
GROUP BY enemy_id
ORDER BY enemies DESC

LIMIT 5

[cdoyle]

that works perfect,  thanks again.