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.

CountOrlokAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

UmeshMySQL Principle Technical Support EngineerCommented:

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
)
0
MacAnthonyCommented:
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;
0
CountOrlokAuthor Commented:
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.
0
Introducing Cloud Class® training courses

Tech changes fast. You can learn faster. That’s why we’re bringing professional training courses to Experts Exchange. With a subscription, you can access all the Cloud Class® courses to expand your education, prep for certifications, and get top-notch instructions.

UmeshMySQL Principle Technical Support EngineerCommented:
Seems to be disk space issue...  especially temporary dir... free some space on /tmp dir and try above
0
MacAnthonyCommented:
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.
0
MacAnthonyCommented:
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
0
MacAnthonyCommented:
Ooops. copy paste issue.

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 points DESC LIMIT 1 ) AS mostpointsid,
( SELECT points FROM gameslog ORDER BY points DESC LIMIT 1 ) AS mostpoints,
( SELECT userid FROM gameslog ORDER BY games DESC LIMIT 1 ) AS mostgamesid,
( SELECT games FROM gameslog ORDER BY games DESC LIMIT 1 ) AS mostgames
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
CountOrlokAuthor Commented:
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!
0
UmeshMySQL Principle Technical Support EngineerCommented:
Seems to be point passing.... disgusting
0
MacAnthonyCommented:
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.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
PHP

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.