StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PONeed to pull data from mysql by unique userid, max rounds, then sort by another value
text
Body
copied!<p>A couple months ago I asked a question about something similiar to this, received some help and thought I had the answer. 3 months later I am seeing this didnt work 100% so I need some more help but I can ask the question better now that I understand more.</p> <p>I have a mysql table with <code>id, userid, rounds, reps, exerciseid</code></p> <p>I need to pull a users highest round specific to the exercise I am pulling for. So if the <code>exerciseid</code> was 8 I would need the users top round for that <code>exerciseid</code> but if the user has the same <code>rounds</code> more than once, which happens a lot, then I need to sort that by the <code>reps</code> to give me a true highest performance. Now, after obtaining those results I need to then sort this data set by <code>rounds</code>,<code>reps</code> so if multiple unique users have the same <code>rounds</code> that is then ordered by <code>reps</code>. </p> <p>Can this be done with pure mysql or am I better off pulling the data and sorting everything with PHP?</p> <pre><code>SELECT max(l.rounds) as rounds, l.reps,l.userid from leaderboard l where l.exerciseid = 8 group by l.userid order by rounds desc, reps desc </code></pre> <p>example of structure First this is a smaple set</p> <pre><code>userid exerciseid rounds reps -- -- -- 1 8 23 10 1 8 23 15 1 8 20 10 2 8 28 19 2 8 15 12 3 8 40 29 results I want userid exerciseid rounds reps -- -- -- 3 8 40 29 2 8 28 19 1 8 23 15 </code></pre>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload