Ranking position of a database entry

[viciousvinyl]

Hi,

 

I have a MySQL query which calculates the ranking of an entry within the database, and it works ok, but it gives an inaccurate ranking when the score of an entry is equal to another entry.

 

Select count(*) as ranking, t1.Rank, t1.ID

FROM Database as t1, Database as t2

WHERE t1.Rank <= t2.Rank

 

This counts all entries with a less than or equal score to that of the entry ID in question.

 

However, if there are two or more equal scores then it will count these entries as part of the the count calculation also.

 

e.g.

 

1

2

3

4

4

4 - this is the entry in question.

 

So, there are SIX entries which are less or equal. This is wrong.

 

The ranking of this track is FOUR because it is FOUR equal - the same as the two entries above it.

 

Can someone help me?

 

Thanks.

 

Richard.

[Philip]

I'm not sure I follow what exactly you're trying to do here...

 

So you want to find the ranking of the ID? Why not just pull the rank column?

[viciousvinyl]

Yes, you didn't understand or I didn't word myself properly.

 

Of course, there's already an index for each record, and each record has an associated score.

 

e.g.

 

Index Score

1        34

2        43

3        14

4        23

5        43

6        43

 

You see, records 2, 5 and 6 have a score of '43', so they are all in fourth place.

 

But the query I'm using registers them all in SIXTH place.

 

Any other suggestions, please let me know.

 

Oh, and I've tried using '<='. This works but not when the record is number 1 ranked.

 

Richard.

[fenway]

The only way I've ever handled this is with user variables -- store the last score, and a related counter.  Increment the counter each time the score is the same, otherwise, reset both.