Re: getting row position from query..

Are you stepping through each row, or do you need to find it for a few specific rows? Chad Day wrote: > > Sending this over to php-general because I think I may be more of a logic > issue than a query issue.. > > Original post: > > CD> I'm trying to determine the percentile value of where a row lies in a > CD> query.. hard to explain, but I'll try. > > CD> I do a select all of my table, sorting it by SCORE. I want to find the > CD> position of a certain row in that select all, get that row #, and then I > can > CD> calculate the percentile fine by dividing that position into the total # > of > CD> rows, then multiplying by 100. > > I'm guessing I need to use mysql_data_seek somehow but I can't wrap my head > around how I'm supposed to use it in this situation, and how I can get the > position of the row to seek to.. the thing I need to get out of this is the > position of where that row lies in that sorted query. > > (Alex.. I tried your 2nd query below as it looked like what I needed, but > returned a syntax error which I couldn't figure out.. :( ) > > Thanks, > Chad > > -----Original Message----- > From: Alexey Borzov [mailto:borz_off@rdw.ru] > Sent: Wednesday, December 06, 2000 3:10 AM > To: Chad Day > Cc: 'php-db@lists.php.net' > Subject: Re: [PHP-DB] auto incrementing rows from query.. > > Greetings, Chad! > > At 06.12.2000, 11:00, you wrote: > > CD> I'm trying to determine the percentile value of where a row lies in a > CD> query.. hard to explain, but I'll try. > > CD> I do a select all of my table, sorting it by SCORE. I want to find the > CD> position of a certain row in that select all, get that row #, and then I > can > CD> calculate the percentile fine by dividing that position into the total # > of > CD> rows, then multiplying by 100. > > CD> my code is like: > > CD> $querysetup = "SELECT SCORE, PID from pictures where VOTES > 100 order > by > CD> SCORE DESC"; > > Ugh. I don't think that I quite get you, but: > > If you want to find "that row" by SCORE, then you should issue something > like > SELECT COUNT(*) FROM pictures WHERE VOTES>100 AND > SCORE>$your_needed_SCORE > > If you want to find "that row" by PID, then > SELECT COUNT(*) FROM pictures WHERE VOTES>100 AND SCORE>(SELECT SCORE > FROM pictures WHERE PID=$your_needed_PID) > > Thus you get the number of rows before "that row" > > Then issue > SELECT COUNT(*) FROM pictures WHERE VOTES>100 > to get _total_ number of rows > > then divide one by the other, multiply by 100 and you're all done. :] > > I'm almost sure that can be done in a more elegant way, though... > > -- > Yours, Alexey V. Borzov, Webmaster of RDW > > -- > PHP Database Mailing List (http://www.php.net/) > To unsubscribe, e-mail: php-db-unsubscribe@lists.php.net > For additional commands, e-mail: php-db-help@lists.php.net > To contact the list administrators, e-mail: php-list-admin@lists.php.net > > -- > PHP General Mailing List (http://www.php.net/) > To unsubscribe, e-mail: php-general-unsubscribe@lists.php.net > For additional commands, e-mail: php-general-help@lists.php.net > To contact the list administrators, e-mail: php-list-admin@lists.php.net