File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
http://aspose.com/file-tools
The moose likes JDBC and the fly likes Query problem with MySQL 5 Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Query problem with MySQL 5" Watch "Query problem with MySQL 5" New topic
Author

Query problem with MySQL 5

Tero Ahonen
Greenhorn

Joined: May 24, 2001
Posts: 26
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.


-Tero
todd runstein
Ranch Hand

Joined: Feb 15, 2005
Posts: 64
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

Joined: May 24, 2001
Posts: 26
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.
Aiglee Castillo
Greenhorn

Joined: Aug 10, 2006
Posts: 27
You can do it using a temporary table:


Then you can drop the table
;)
todd runstein
Ranch Hand

Joined: Feb 15, 2005
Posts: 64
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?
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Query problem with MySQL 5