Win a copy of Node.js Design Patterns: Design and implement production-grade Node.js applications using proven patterns and techniques this week in the Server-Side JavaScript and NodeJS forum!
  • Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Ron McLeod
  • Rob Spoor
  • Tim Cooke
  • Junilu Lacar
Sheriffs:
  • Henry Wong
  • Liutauras Vilda
  • Jeanne Boyarsky
Saloon Keepers:
  • Jesse Silverman
  • Tim Holloway
  • Stephan van Hulst
  • Tim Moores
  • Carey Brown
Bartenders:
  • Al Hobbs
  • Mikalai Zaikin
  • Piet Souris

Query problem with MySQL 5

 
Greenhorn
Posts: 26
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
I have a small query problem that I cannot solve.

I have an result table which contain all game results

mysql> select * from result;
+----+---------------------+------------------+-----------+---------+-------+
| id | created | points | player_id | game_id | round |
+----+---------------------+------------------+-----------+---------+-------+
| 1 | 2006-06-13 12:52:30 | 3811.88333333333 | 4 | 3 | 0 |
| 2 | 2006-06-13 12:53:33 | 3804.55 | 4 | 3 | 0 |
| 3 | 2006-06-13 14:17:42 | 0 | 5 | 3 | 0 |
| 4 | 2006-06-15 00:12:29 | 3826.05 | 5 | 3 | 0 |
| 5 | 2006-06-15 00:13:37 | 1893.5 | 5 | 3 | 0 |
| 6 | 2006-06-15 00:14:12 | 1891.16666666667 | 5 | 3 | 0 |
| 7 | 2006-07-28 08:34:27 | 1913.05 | 1 | 3 | 0 |
| 8 | 2006-07-28 08:36:22 | 1865.46666666667 | 1 | 3 | 0 |
| 9 | 2006-07-28 08:38:11 | 0 | 1 | 3 | 0 |
| 10 | 2006-07-28 08:38:49 | 1919.38333333333 | 1 | 3 | 0 |
| 11 | 2006-07-28 21:36:25 | 0 | 6 | 3 | 0 |
| 12 | 2006-07-31 01:08:56 | 0 | 1 | 3 | 0 |
| 13 | 2006-07-31 02:01:55 | 1841.61666666667 | 1 | 3 | 0 |
| 14 | 2006-07-31 02:05:47 | 1922.73333333333 | 1 | 3 | 0 |
| 15 | 2006-07-31 02:08:12 | 1879.31666666667 | 1 | 3 | 0 |
| 16 | 2006-08-09 21:58:05 | 0 | 1 | 3 | 0 |
| 17 | 2006-08-09 21:58:13 | 0 | 1 | 3 | 0 |
| 18 | 2006-08-09 23:27:10 | 1579.23333333333 | 1 | 3 | 4 |
| 19 | 2006-08-11 06:17:44 | 0 | 1 | 3 | 5 |
| 20 | 2006-08-11 07:23:04 | 3748.96666666667 | 1 | 3 | 22 |
| 21 | 2006-08-11 07:28:26 | 3517.98333333333 | 1 | 3 | 23 |
| 22 | 2006-08-11 07:28:36 | 5258.61666666667 | 1 | 3 | 23 |
| 23 | 2006-08-11 07:28:41 | 6908.61666666667 | 1 | 3 | 23 |
| 24 | 2006-08-11 07:30:26 | 3782.01666666667 | 1 | 3 | 25 |
| 25 | 2006-08-11 07:30:31 | 5610.78333333333 | 1 | 3 | 25 |
| 26 | 2006-08-11 07:30:34 | 7397.65 | 1 | 3 | 25 |
| 27 | 2006-08-11 07:30:34 | 9173.66666666667 | 1 | 3 | 25 |
| 28 | 2006-08-11 07:30:35 | 10941.3333333333 | 1 | 3 | 25 |
| 29 | 2006-08-11 07:30:35 | 12701.3333333333 | 1 | 3 | 25 |
| 30 | 2006-08-11 07:30:36 | 14454.8166666667 | 1 | 3 | 25 |
| 31 | 2006-08-11 07:30:36 | 16202.3 | 1 | 3 | 25 |
| 32 | 2006-08-11 07:30:36 | 17944.2666666667 | 1 | 3 | 25 |
| 33 | 2006-08-11 07:30:39 | 19645.6833333333 | 1 | 3 | 25 |
| 34 | 2006-08-11 07:30:39 | 21340.9166666667 | 1 | 3 | 25 |
| 35 | 2006-08-11 07:30:40 | 23030.3166666667 | 1 | 3 | 25 |
| 36 | 2006-08-11 07:30:43 | 24666.1333333333 | 1 | 3 | 25 |
| 37 | 2006-08-11 07:30:44 | 26285.4333333333 | 1 | 3 | 25 |
| 38 | 2006-08-11 07:35:21 | 3875.53333333333 | 1 | 3 | 27 |
+----+---------------------+------------------+-----------+---------+-------+

I have a query which I use the read the position on one invidual player..

mysql> select count(*) from result where points >= (SELECT max(points) from result where game_id = 3 AND player_id = 4);
+----------+
| count(*) |
+----------+
| 18 |
+----------+

But this result is wrong . I would like to check the position of a player from distinct results set.

So with this query I can created the distict result list with max points on each player

mysql> select distinct player_id, max(points) as maxp from result group by player_id order by maxp desc;
+-----------+------------------+
| player_id | maxp |
+-----------+------------------+
| 1 | 26285.4333333333 |
| 5 | 3826.05 |
| 4 | 3811.88333333333 |
| 6 | 0 |
+-----------+------------------+

How can I join the which I use to read the position of a player with below query.
 
Ranch Hand
Posts: 64
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Tero,

I'd love to help you with this - SQL problems are always fun to solve - but I don't get what you're trying to do.

First of all, 18 is correct. What answer were you expecting?

Second, I don't understand the join you're trying to do.

Perhaps it would help if you provided the results you're trying to get out of the table. Maybe come up with the resultset you're looking for - then, perhaps we can help you write the SQL to produce the resultset.
 
Tero Ahonen
Greenhorn
Posts: 26
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Correct result should be 3, because only best results is counter from each player. Se the query to get players position in list should be executed agains result set created with something like this: "select * from result group by player_id order by points desc"

So first I need results list which cointain only best result from each player and from there I need to read players position in list.
 
Greenhorn
Posts: 27
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
You can do it using a temporary table:


Then you can drop the table
;)
 
todd runstein
Ranch Hand
Posts: 64
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
So, for game 3, would something like this work?

select player_id, max(points) as points
from result where game_id = 3
group by player_id
order by points;

My "order by" statement may not work in your db as is, but does the general idea work? This will list all the players who played game 3, and put them
in order from most points to least. Is that closer to what you're looking for?
 
You showed up just in time for the waffles! And this tiny ad:
Building a Better World in your Backyard by Paul Wheaton and Shawn Klassen-Koop
https://coderanch.com/wiki/718759/books/Building-World-Backyard-Paul-Wheaton
reply
    Bookmark Topic Watch Topic
  • New Topic