Re: Query help, please..
On Dec 11, 2007 8:38 AM, Anders Norrbring [EMAIL PROTECTED] wrote: I'm looking at a situation I haven't run into before, and I'm a bit puzzled by it. I have this table structure: Table USERS: userid, class Table OBJECT: userid, class, result Now I want to query the database for a certain user's result in a specified class, which is very, very easy. No problems. But, I also want to find out the user's position relative to others depending on the result. So, if the specified user's result is the 9:th best of all of the users, I want to have a reply from the DB query that say he has position number 9. I really can't figure out how to do that... Somehow I have to make MySQL calculate the position based on the value in the result column. Take a look at http://arjen-lentz.livejournal.com/55083.html . Very similar ideas in play, though you also have a join. The basic idea is that you do a count on the number of users that have a lower score. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Query help, please..
On Dec 11, 2007, at 10:46 AM, Rob Wultsch wrote: On Dec 11, 2007 8:38 AM, Anders Norrbring [EMAIL PROTECTED] wrote: I'm looking at a situation I haven't run into before, and I'm a bit puzzled by it. I have this table structure: Table USERS: userid, class Table OBJECT: userid, class, result Now I want to query the database for a certain user's result in a specified class, which is very, very easy. No problems. But, I also want to find out the user's position relative to others depending on the result. So, if the specified user's result is the 9:th best of all of the users, I want to have a reply from the DB query that say he has position number 9. I really can't figure out how to do that... Somehow I have to make MySQL calculate the position based on the value in the result column. Take a look at http://arjen-lentz.livejournal.com/55083.html . Very similar ideas in play, though you also have a join. The basic idea is that you do a count on the number of users that have a lower score. Is there any reason you wouldn't want to count the people in front of you and add 1 to get your place in line? It seems like depending on where you are, that may be a shorter number to count :) But I don't know anything about how to do stuff off of separate tables yet still trying to grasp that :) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- Jason Pruim Raoset Inc. Technology Manager MQC Specialist 3251 132nd ave Holland, MI, 49424 www.raoset.com [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Query help, please..
Anders, I also want to find out the user's position relative to others depending on the result. For a given pUserID, something like this? SELECT userid,result,rank FROM ( SELECT o1.userid,o1.result,COUNT(o2.result) AS rank FROM object o1 JOIN object o2 ON o1.result o2.result OR (o1.result=o2.result AND o1.userid=o2.userid) GROUP BY o1.userid,o1.result ) WHERE userid = pUserID; PB - Anders Norrbring wrote: I'm looking at a situation I haven't run into before, and I'm a bit puzzled by it. I have this table structure: Table USERS: userid, class Table OBJECT: userid, class, result Now I want to query the database for a certain user's result in a specified class, which is very, very easy. No problems. But, I also want to find out the user's position relative to others depending on the result. So, if the specified user's result is the 9:th best of all of the users, I want to have a reply from the DB query that say he has position number 9. I really can't figure out how to do that... Somehow I have to make MySQL calculate the position based on the value in the result column. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Query help please!
Do you have another table with all the birthdates in it? -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] Sent: Tuesday, March 05, 2002 12:05 PM To: [EMAIL PROTECTED] Subject: Query help please! I need help writing query that would give me parent categories of catID from categories table. For example, if catID=030 then it should give me: Birthday | Special Birthday | Special Birthday If catID=028 Birthday | General Birthday | NULL desc categories +---+--++ | catID | parentID | catName| +---+--++ | 001 | 000 | Birthday | | 002 | 000 | Get Well | | 003 | 000 | Special Occasions | | 038 | 029 | 40th | | 037 | 029 | 30th | | 036 | 029 | 21st | | 035 | 029 | 16th | | 029 | 001 | Special Birthday | | 028 | 001 | General Birthday | | 030 | 029 | Inspirational | | 045 | 001 | Children's Birthday| | 046 | 045 | 1st| +---+--++ Pinkesh - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: Query help please!
One more thing... What are you using to pull the data? PHP? Perl? Etc? -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] Sent: Tuesday, March 05, 2002 12:05 PM To: [EMAIL PROTECTED] Subject: Query help please! I need help writing query that would give me parent categories of catID from categories table. For example, if catID=030 then it should give me: Birthday | Special Birthday | Special Birthday If catID=028 Birthday | General Birthday | NULL desc categories +---+--++ | catID | parentID | catName| +---+--++ | 001 | 000 | Birthday | | 002 | 000 | Get Well | | 003 | 000 | Special Occasions | | 038 | 029 | 40th | | 037 | 029 | 30th | | 036 | 029 | 21st | | 035 | 029 | 16th | | 029 | 001 | Special Birthday | | 028 | 001 | General Birthday | | 030 | 029 | Inspirational | | 045 | 001 | Children's Birthday| | 046 | 045 | 1st| +---+--++ Pinkesh - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
