Thread: select max()

Hello,

I have this table where I store player results for each week:

# select * from pref_money limit 5;
       id       | money |   yw
----------------+-------+---------
 OK32378280203  |   -27 | 2010-44
 OK274037315447 |   -56 | 2010-44
 OK19644992852  |     8 | 2010-44
 OK21807961329  |   114 | 2010-44
 FB1845091917   |   774 | 2010-44
(5 rows)

I'm trying to find the winners for each week and
also how many times the player has won,
so that I can display a number of medals
at his or her profile page.

I'm trying:

# select id, money from pref_money where money in
   (select max(money) from pref_money group by yw);
       id       | money
----------------+-------
 DE8048         |  3927
 VK91770810     |  6133
 DE7115         |  6655
 OK252342810632 |  8053
 OK228530000997 |  1013
(5 rows)

But I'm worried that "where in" will return false positives
(for the cases with the same money values)
and also I don't know how to count the repeated winners?

Thank you
Alex

Ok, it is

# select id from pref_money where money in (select max(money) from
pref_money group by yw);
       id
----------------
 DE8048
 VK91770810
 DE7115
 OK252342810632
 OK228530000997
(5 rows)

And to see how many times a player has won is:

# select count(id) from pref_money where id='DE7115' and money in
(select max(money) from pref_money group by yw);
 count
-------
     1
(1 row)

My only problem is how to prevent false positives,
when the money value in 2 months is the same and
in 1 of the months it is the max value, but in the
other month it is not the max value and has another id.
Then the latter id will be falsely indicated as "winner"...

Regards
Alex