The most efficient way to find position of a returned row relative to the total number of rows?

[eluser]Dandy_andy[/eluser]
Suppose I have 100 rows in a table and each row has a value/column X which varies for each row. How can I found out what position a particular row is relative to when ordered by X? I am looking to do this so that I can output a position value based on the total number of rows ordered by value X. Is there a simple way to do this using the active record class and bearing in mind the number or rows will increase as entries are added?

Any help appreciated. Thanks in advance.

[eluser]PhilTem[/eluser]
The simplest solution would be to have an auto-incrementing column that you can use for doing this. On the other, when looping over the array for outputting it you can run a counter variable that counts the current position. And you know how many results you have by count_all_results();

Basically, the second thing would be

Code:

$results = $this->model->get();
$count_results = count($results);

for ( $i = 0; $i < $count_results; $i++ )
{
  // Here, $i is the position in the array of results and $count_results would be 100
}

But I guess there's no other way of doing what you want or, to my knowledge, no real way

[eluser]Dandy_andy[/eluser]
Hi PhilTem,
That's the way I was thinking of doing it but was concerned about performance particularly if there are hundreds or thousands of users doing the same thing at the same time. So far, I haven't been able to find an easy way to do this although I'm convinced there might be a MYSQL query that could solve this for me.
Thanks

[eluser]PhilTem[/eluser]
You can always cache the query result or the formatted view so you won't have to be concerned about performance that much