Jump to content


Photo

Get most frequent data in column


  • Please log in to reply
3 replies to this topic

#1 Kingskin

Kingskin
  • Members
  • PipPip
  • Member
  • 18 posts

Posted 20 October 2005 - 12:08 AM

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?

#2 Kingskin

Kingskin
  • Members
  • PipPip
  • Member
  • 18 posts

Posted 22 October 2005 - 02:08 AM

Anyone? :(

#3 ryanlwh

ryanlwh
  • Staff Alumni
  • Advanced Member
  • 511 posts

Posted 22 October 2005 - 04:38 AM

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--]
Please use EDIT * 100...
Please use
or [php] * 1000...

PLEASE READ THE POSTED SOLUTIONS CAREFULLY * 1000000...

#4 Kingskin

Kingskin
  • Members
  • PipPip
  • Member
  • 18 posts

Posted 22 October 2005 - 07:27 AM

Worked a treat, thanks mate :-)




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users