Jump to content
Updating IPB tonight Read more... ×

Archived

This topic is now archived and is closed to further replies.

Kingskin

Get most frequent data in column

Recommended Posts

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?

Share this post


Link to post
Share on other sites

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--]

Share this post


Link to post
Share on other sites

×

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.