Shared Ranking problem

[doodalf]

Hi,

I'm wondering if someone can help me with a problem I've been trying to solve for almost a week now which concerns shared ranking (i.e. whilst I am trying to rank things in order of one field, if two or more entries share the same value then they receive the same ranking).

I have a MySQL database as follows:-

racedate | racetime | racecourse | horsename | forecast | rank | id

The racedate for the purposes of this will always be the same. The racetime and racecourse together identify the race in question.

forecast is the price given to each horse (this has already been entered at this stage) and this is what needs to have the shared ranking done on it to be stored in "rank".

id is just the unique index for each entry in the table.

The problem is further compounded by the fact that I wish to rank each race within the table seperately.

An example of what I am trying to do is given by this illustration:-
RACE - HORSE - FORECAST - RANK
Race 1 - Horse A - 8 - RANKING SHOULD BE 2
Race 1 - Horse B - 8 - RANKING SHOULD BE 2
Race 1 - Horse C - 2 - RANKING SHOULD BE 1

Race 2 - Horse D - 4 - RANKING SHOULD BE 1
Race 2 - Horse E - 5 - RANKING SHOULD BE 2

Any assistance would be greatly appreciated.

[doodalf]

By the way, I'm using PHP to program.

[smantscheff]

It is not clear what your problem is. Or how you tried to solve it.

You can sort and group your table by race and forecast. If that's what you want, try
select * from races order by race, forecast

If you want an explicit "rank", count the number of horses with higher forecasts in the same race and add 1:
select a.race, a.horse, a.forecast, (select count(*) from races b where a.race=b.race and a.forecast > b.forecast) + 1 as theRank from races a;

[doodalf]

You, sir, are a genius. Thank you very much. Would you care for my first-born? :)

[doodalf]

Ahh. No. I thought that this worked at first but it doesn't quite.

Basically, it is producing an output like this:-

Horse - Forecast - Rank
A - 5 - 1
B - 5 - 1
C - 7 - 3
D - 8 - 4

When I was looking for:-

Horse - Forecast - Rank
A - 5 - 1
B - 5 - 1
C - 7 - 2
D - 8 - 3

I.e. the first two are shared 1 and then the next highest is 2 as opposed to 3.

Does this make sense?

[doodalf]

Worked it out, it needed

select count(DISTINCT b.forecast) from races b

instead of

select count(*) from races b

Many thanks for your help - couldn't have done it without you.

[smantscheff]

Thanks for sharing the results. I saw the problem but did not come up with a solution right away - glad that you've found it.