Page 1 of 1

Finding position of a row in a query

Posted: Thu Apr 10, 2008 4:11 pm
by impulse()
If I have 100 rows output from a query and they don't have a primary key or an ID column then how would I find a column position in that list? I assume there's a way to output a incremented number as a column in a query but I can't think how I would do that.

Any help appreciated.

Thanks,

Re: Finding position of a row in a query

Posted: Thu Apr 10, 2008 4:57 pm
by John Cartwright
Short answer: no

Another short answer: add a primary key?

I have never come across a situation that allowed for a table to not use a PK. Even if you don't plan on using it, its purposes serves to identify the row.

Re: Finding position of a row in a query

Posted: Thu Apr 10, 2008 5:11 pm
by impulse()
It's not my table to change. They do have primary keys but after a where clause and group by the primary key number isn't the row position number anymore.

Re: Finding position of a row in a query

Posted: Thu Apr 10, 2008 5:38 pm
by John Cartwright
impulse() wrote:It's not my table to change. They do have primary keys but after a where clause and group by the primary key number isn't the row position number anymore.
from a query and they don't have a primary key or an ID column
so they do or they don't?!??! :banghead:

You want to know its relative positive in the result set or its relative positive to the entire database?

The former,

Code: Select all

$i = 0;
while ($row = mysql_fetch_assoc($result)) {
   ++$i;
    echo 'Your row: '. $i .'<br />';
}

Care to post what you've tried so far?

Re: Finding position of a row in a query

Posted: Fri Apr 11, 2008 2:11 am
by impulse()
I was hoping to do it all in a single query. I'm looking for the relative position in the result set. Because I'd already be inside a loop with a userID to run the query that you'd posted then I could be running your query upto 50 times, which is what I'm trying to reduce.

I have this at the moment:

Code: Select all

SELECT MAX(gs.score) as highScore, gl.gname, gl.gid, COUNT(*) as played, gs.datescored
         FROM games_scores AS gs 
         JOIN games_list AS gl ON gs.gid = gl.gid
         WHERE mid = {$_GET['uid']}
         GROUP BY score, gl.gname, gl.gid
And then while inside that loop I'm running:

Code: Select all

 
$query = mysql_query("SELECT name, mid
                      FROM games_scores
                     WHERE gid = {$r['gid']}
                     ORDER BY score DESC");
 
while($r2 = mysql_fetch_array($query)) {
  if($r2['mid'] != $_GET['uid'])
     ++$i;
  else {
    $pos = $i;
    continue;
  }
}
 
But as you may see, I'm running the 2nd query as many times are there are rows from the first query. I was hoping to do everything in just a single query.