Sub-queries

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:

Sub-queries

Post by impulse() »

I'm looking for a solution and I believe sub-quering is the answer as I have slight knowledge of how they work. I'm running the following query:

Code: Select all

SET @pos=0;
SELECT @pos:=@pos+1 AS pos, mid, score
FROM games_scores
WHERE gid = {$r['gid']}
ORDER BY score DESC
What this does is return, in score descending order and with a position column next to each, a score board. Because there can be over 1000 rows returned I don't want to have to put all those rows into a PHP array just so I can fetch out 1 row from it. I was hoping I could run the above query as a sub-query for another query which only fetches the row where the user ID is equal to a specified value.

Hopefully somebody can confirm whether this is possible with sub-queries or suggest a more efficient way of doing this.

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

Re: Sub-queries

Post by impulse() »

A second approach I've thought of is using the 'having' clause.

EG:

Code: Select all

  1. SET @pos=0;
SELECT @pos:=@pos+1 AS pos, mid, score
FROM games_scores
WHERE gid = {$r['gid']}
ORDER BY score DESC
HAVING mid = <id>
But this always returns the position as 1. As if it's not returning all the rows.
impulse()
Forum Regular
Posts: 748
Joined: Wed Aug 09, 2006 8:36 am
Location: Staffordshire, UK
Contact:

Re: Sub-queries

Post by impulse() »

I have been given a few suggestions for this incase anybody else falls across this post:

Firstly:
http://arjen-lentz.livejournal.com/5508 ... 419#t37419

Secondly (MySQL 4.1 or above):

Code: Select all

SELECT COUNT(*) + 1 AS rank
  FROM games_scores AS g
 WHERE gid = $gid
   AND score >
       ( SELECT score
           FROM games_scores
          WHERE gid = g.gid
            AND mid = $mid )
Thirdly (Below MySQL 4.1):

Code: Select all

SELECT COUNT(bar.gid) + 1 AS rank
  FROM games_scores AS foo
LEFT OUTER
  JOIN games_scores AS bar
    on bar.gid   = foo.gid
   and bar.score > foo.score
 WHERE foo.gid = 10
   AND foo.mid = 1
I have tested them all and they all work OK.

Thanks,
Post Reply