Get most frequent data in column

[Kingskin]

Hi,

 

My db table (episode_result) has these columns:

 

player_id

position

 

I'm trying to find which player_id occurs most often where position = 1.

Eg, if i've got:

 

player_id | position
          |
Bill      |     1
Bill      |     1
Ben       |     1
Bill      |     3
Ben       |     2
Bill      |     1

 

I want to return 'Bill'

 

I've got this code:

 

SELECT TOP 1 player_id
FROM (SELECT COUNT(*) AS myNewColumn, player_id
FROM episode_result
WHERE position = 1
GROUP BY player_id) DERIVEDTBL
ORDER BY myNewColumn DESC";

 

But unfortunately it doesn't work. Can anyone help me out with this please?

[Kingskin]

Anyone?

[ryanlwh]

untested

[!--sql--][div class=\'sqltop\']SQL[/div][div class=\'sqlmain\'][!--sql1--][span style=\'color:blue;font-weight:bold\']SELECT[/span] COUNT(position) AS myNewColumn, player_id

FROM episode_result WHERE position = 1

GROUP BY player_id ORDER BY myNewColumn DESC LIMIT 1 [!--sql2--][/div][!--sql3--]

[Kingskin]

Worked a treat, thanks mate :-)