Question regarding 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
User avatar
Josh1billion
Forum Contributor
Posts: 316
Joined: Tue Sep 11, 2007 3:25 pm

Question regarding a query...

Post by Josh1billion »

(Subject is a little vague, but it's hard to explain this in one line)

Say I have the following query:

Code: Select all

$result = mysql_query("SELECT users1.name, users1.age, COUNT(users2.age) AS Rank
         FROM users users1
         JOIN users users2 ON users1.age < users2.age OR (users1.age=users2.age and users1.name = users2.name)
                        WHERE users1.name='Bob'
         GROUP BY users1.name, users1.age
         ORDER BY users1.age DESC, users1.name DESC
         ;
         
         ") or die ("error 3: " . mysql_error());
   
   $result2 = mysql_fetch_array($result);
   print "Bob is the " . $result2['Rank'] . " oldest person<BR>";
The purpose of the query is to just return the position (rank) that an entry will be in an ORDER BY query, ordered by age. The end result is that it prints that Bob is the ____ oldest person in the database.

How can I add into that query a field "gender", so that it will only factor in users that are `gender`='Male' so that I can find out that Bob is the __ oldest man instead of the ___ oldest person?

I tried many combinations of putting a WHERE clause around in that query but most of my attempts ended up with an error; I couldn't figure out where exactly in the query it should go.
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

Your query seems overly complicated. What version of MySQL are you running this against?
User avatar
Josh1billion
Forum Contributor
Posts: 316
Joined: Tue Sep 11, 2007 3:25 pm

Post by Josh1billion »

MySQL 5
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

I would imagine a subquery would be faster in this instance.. possibly faster still would be two separate queries.. one for Bob's age and one for all people younger/older than Bob.
User avatar
Josh1billion
Forum Contributor
Posts: 316
Joined: Tue Sep 11, 2007 3:25 pm

Post by Josh1billion »

Hmm.. the two query idea could possibly work, but the rank/position that it would report in that situation MIGHT be off in situations where several users have the same age. So Bob could be tied for the place of the 53rd oldest user (along with a few other people his same age), and if that's the case, the two-query method would be telling us the position that he's tied for (53) rather than telling us what the real SELECT ORDER BY rank/position will be, which could be end up causing a few problems with what I have planned (my plan is something more complicated than just printing "Bob is the ___ oldest person" as seen in my example :P).

What's the subquery idea you were talking about?

Oh, and so you know what my basic idea is for an end result: the reason I need to find the rank/position that a record will be (finding that rank/position is what the original query I posted above does) is that I want to select the records that are "near" the record with a later query. In other words, if Bob is the 50th-oldest person in the table, my final end-result query will be a select query that gives me the names of the people who are, say, 45th-to-55th oldest (including Bob). Hope that makes sense. :) If you Feyd or anyone else knows of a better way to accomplish this end result, please let me know. :)
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

What says that you couldn't select people the same age as Bob? However, your existing query appears not select anyone the same age as Bob.
User avatar
Josh1billion
Forum Contributor
Posts: 316
Joined: Tue Sep 11, 2007 3:25 pm

Post by Josh1billion »

feyd wrote:What says that you couldn't select people the same age as Bob?
Read my first paragraph of my last post.
feyd wrote:However, your existing query appears not select anyone the same age as Bob.
Right, my first query is meant to find the rank/position Bob will be of the order in which an ORDER BY age query would be performed. It does that correctly, but I just want to know how to add an extra parameter (gender) into the factor so that only "male" genders would be selected in the ordering process.
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

Josh1billion wrote:Read my first paragraph of my last post.
I did; several times, no less. Which resulted in my question.
User avatar
Josh1billion
Forum Contributor
Posts: 316
Joined: Tue Sep 11, 2007 3:25 pm

Post by Josh1billion »

Oh wait, I think I see the idea... hmmmmmm..

Is this what you were meaning? (it looks like this would work although it may be a little slow-- I wonder how slow it would be in situations where the second query returns, say, 50,000 records)

Code: Select all

$result = mysql_query("SELECT `name` FROM `users` WHERE `age` > '$bobs_age'");
$users_older_than_bob = mysql_num_rows($result);

$result = mysql_query("SELECT `name` FROM `users` WHERE `age`='$bobs_age'");

while($current_user = mysql_fetch_array($result))
{
    if ($current_user['name'] != "Bob")
        $users_older_than_bob++;
}

$rank = $users_older_than_bob + 1;
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

I don't see why it would need to return the names. A count of them should suffice.
Post Reply