Finding position of a row in a query

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
impulse()
Forum Regular
Posts: 748
Joined: Wed Aug 09, 2006 8:36 am
Location: Staffordshire, UK
Contact:

Finding position of a row in a query

Post 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,
User avatar
John Cartwright
Site Admin
Posts: 11470
Joined: Tue Dec 23, 2003 2:10 am
Location: Toronto
Contact:

Re: Finding position of a row in a query

Post 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.
impulse()
Forum Regular
Posts: 748
Joined: Wed Aug 09, 2006 8:36 am
Location: Staffordshire, UK
Contact:

Re: Finding position of a row in a query

Post 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.
User avatar
John Cartwright
Site Admin
Posts: 11470
Joined: Tue Dec 23, 2003 2:10 am
Location: Toronto
Contact:

Re: Finding position of a row in a query

Post 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?
impulse()
Forum Regular
Posts: 748
Joined: Wed Aug 09, 2006 8:36 am
Location: Staffordshire, UK
Contact:

Re: Finding position of a row in a query

Post 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.
Post Reply