Page 1 of 1
Question regarding a query...
Posted: Fri Dec 21, 2007 9:23 am
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.
Posted: Fri Dec 21, 2007 9:32 am
by feyd
Your query seems overly complicated. What version of MySQL are you running this against?
Posted: Fri Dec 21, 2007 10:19 am
by Josh1billion
MySQL 5
Posted: Fri Dec 21, 2007 10:32 am
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.
Posted: Fri Dec 21, 2007 8:15 pm
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

).
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.

Posted: Fri Dec 21, 2007 8:32 pm
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.
Posted: Sat Dec 22, 2007 2:04 pm
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.
Posted: Sun Dec 23, 2007 9:53 am
by feyd
Josh1billion wrote:Read my first paragraph of my last post.
I did; several times, no less. Which resulted in my question.
Posted: Sun Dec 23, 2007 10:26 am
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;
Posted: Sun Dec 23, 2007 11:00 am
by feyd
I don't see why it would need to return the names. A count of them should suffice.