CountOrlok
asked on
How can I select multiple MAX() values withphp/mysql?
Here is my db structure (simplified, of course):
userid games wins points
123 17 3 58
478 15 2 45
368 14 2 71
219 13 1 62
What I want to return is the MAX value for games, wins, and points along with the playerid who earned it.
Sample required output:
mostwins mostwinsuserid mostpoints mostpointsuserid mostgames mostgamesuserid
3 123 71 368 17 123
Any help is appreciated.
userid games wins points
123 17 3 58
478 15 2 45
368 14 2 71
219 13 1 62
What I want to return is the MAX value for games, wins, and points along with the playerid who earned it.
Sample required output:
mostwins mostwinsuserid mostpoints mostpointsuserid mostgames mostgamesuserid
3 123 71 368 17 123
Any help is appreciated.
Assuming the table name is gameslog, this should work.
SELECT t1.wins AS mostwins, t1.userid AS mostwinsid, t2.points AS mostpoints, t2.userid AS mostpointsid, t3.games AS mostgames, t3.userid AS mostgamesid FROM gameslog t1, gameslog t2, gameslog t3 ORDER BY t1.wins DESC, t2.points DESC, t3.games DESC LIMIT 1;
SELECT t1.wins AS mostwins, t1.userid AS mostwinsid, t2.points AS mostpoints, t2.userid AS mostpointsid, t3.games AS mostgames, t3.userid AS mostgamesid FROM gameslog t1, gameslog t2, gameslog t3 ORDER BY t1.wins DESC, t2.points DESC, t3.games DESC LIMIT 1;
ASKER
MacAnthony -
I tried your suggestion first as it seemed cleaner, but I got this error: mysql #1030 - Got error 28 from storage engine.
ushastry - your solutionthrew up some invalid syntax errors, but I cannot find where the problem is.
I tried your suggestion first as it seemed cleaner, but I got this error: mysql #1030 - Got error 28 from storage engine.
ushastry - your solutionthrew up some invalid syntax errors, but I cannot find where the problem is.
Seems to be disk space issue... especially temporary dir... free some space on /tmp dir and try above
From some quick googling, it seems that an error 28 is a ran out of disk space issue. How many records are in this table?
To save space, each result could be returned from a subselect. Mine might be causing an issue since it's a union join, the result would be number of records cubed since there are 3 tables referenced. If there are 1000 records in there, the ending result set would be 1 billion.
To save space, each result could be returned from a subselect. Mine might be causing an issue since it's a union join, the result would be number of records cubed since there are 3 tables referenced. If there are 1000 records in there, the ending result set would be 1 billion.
Using subqueries:
SELECT
( SELECT userid FROM gameslog ORDER BY wins DESC LIMIT 1 ) AS mostwinsid,
( SELECT wins FROM gameslog ORDER BY wins DESC LIMIT 1) AS mostwins,
( SELECT userid FROM gameslog ORDER BY wins DESC LIMIT 1 ) AS mostpointsid,
( SELECT points FROM gameslog ORDER BY wins DESC LIMIT 1 ) AS mostpoints,
( SELECT userid FROM gameslog ORDER BY wins DESC LIMIT 1 ) AS mostgamesid,
( SELECT games FROM gameslog ORDER BY wins DESC LIMIT 1 ) AS mostgames
SELECT
( SELECT userid FROM gameslog ORDER BY wins DESC LIMIT 1 ) AS mostwinsid,
( SELECT wins FROM gameslog ORDER BY wins DESC LIMIT 1) AS mostwins,
( SELECT userid FROM gameslog ORDER BY wins DESC LIMIT 1 ) AS mostpointsid,
( SELECT points FROM gameslog ORDER BY wins DESC LIMIT 1 ) AS mostpoints,
( SELECT userid FROM gameslog ORDER BY wins DESC LIMIT 1 ) AS mostgamesid,
( SELECT games FROM gameslog ORDER BY wins DESC LIMIT 1 ) AS mostgames
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
There are 2,700 records in the table with about 10 being added each day.
MacAnthony, your latest solution seems to do the trick. Thanks!
MacAnthony, your latest solution seems to do the trick. Thanks!
Seems to be point passing.... disgusting
I appreciate the completely unnecessary and unprofessional comment.
If you have a concern, you can certainly bring it up to the moderators and keep the tactless comments out of the posts.
If you have a concern, you can certainly bring it up to the moderators and keep the tactless comments out of the posts.
try this.......
table name: sports
SELECT * FROM(
(SELECT MAX(wins) AS mostwins FROM sports)A,
(SELECT userid AS mostwinsuserid FROM sports WHERE wins = ( SELECT MAX(wins) FROM sports))B,
(SELECT MAX(points) AS mostpoints FROM sports)C,
(SELECT userid AS mostpointsuserid FROM sports WHERE points = ( SELECT MAX(points) FROM sports))D,
(SELECT MAX(games) AS mostgames FROM sports)E,
(SELECT userid AS mostgamesuserid FROM sports WHERE games = ( SELECT MAX(games) FROM sports))F
)