index retrieval

this is probably a dumb question but ...

I want to retrieve the index sequence of a field - preferably without retrieving the whole table

so if I have a data item called score which is indexed and contains a numeric value, I want to know it is say the 5th highest in the table.

I can do a ORDER by score DESC, but this is going to retrieve the whole table.

Is there a way to retrieve the sequence directly from the index ?
thanks .. Mike

SELECT score FROM table ORDER BY score DESC LIMIT 5


Note, you can pick and choose your fields, instead of doing SELECT * you can do SELECT field1, field2, etc...

thanks - but I need to clarify

5th was just an example - I dont know the ranking for each record which means I need to read the whole table in score sequence until I find the sequence for this record ?

as I do this a lot - I am looking for a way to optimise the retrieval.

Mike

Or if you ONLY want the fifth element:

Note, this only works on MySQL version 4.1 and newer. In earlier versions, subqueries weren't supported yet.

So what you actually want is a way to retrieve the rank of a know item?
try this:

Where "id = 'wantedid' can be replaced by anything that identifies the record you want the ranking for.

thanks itsacon - thats very helpful

Good. that was my intention