My db table (episode_result) has these columns:
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?