Databases
StackOverflow2013
Postsdbo
PO
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PO
    primarykey
    data
    text
    <p>This query will select the highest points for each user:</p> <pre><code>select uid, max(`points`) from members group by uid </code></pre> <p>and this will select the maximum id where the user has the maximum points:</p> <pre><code>select uid, max(id) from members where (uid, `points`) in (select uid, max(`points`) from members group by uid) group by uid </code></pre> <p>and this is the final query that you need:</p> <pre><code>select members.* from members where (uid, id) in ( select uid, max(id) from members where (uid, `points`) in (select uid, max(`points`) from members group by uid) group by uid) </code></pre> <p>that shows:</p> <pre><code>ID UID POINT DATE TIME 2 2 10 2012-11-29 11:38:12 3 1 10 2012-12-02 05:15:01 6 3 5 2012-12-04 12:18:30 </code></pre> <p>this will also give the same result, and looks simpler:</p> <pre><code>SELECT s.* FROM (SELECT members.* FROM members ORDER BY uid, points desc, id desc) s GROUP BY uid </code></pre> <p>I think that it will always work, but it's not documented!</p> <p>A little explanation for the last query: MySql allows you to select nonaggregated fields in a group by query. Here we are grouping by <code>uid</code> but selecting all columns: the documentation says that the values of the nonaggregated columns will be undetermined (it can be <strong>any</strong> value inside the group) but in fact MySql just returns the first encountered value. And since we are applying a group by with nonaggregated columns on an ordered subquery, the first encountered value is what you need.</p>
    singulars
    1. This table or related slice is empty.
    1. POSelect the highest last row
      singulars
      1. PTQuestion
    1. PTAnswer
    1. USfthiella
    1. USfthiella
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. POSelect the highest last row
      singulars
      1. PTQuestion
    1. This table or related slice is empty.
    1. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    3. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTAcceptedByOriginator
    1. COit seems to OK after a few name changes
      singulars
      1. PO
      1. USGery
    2. COI like simplicity but it's too simple to work but it looks to work :) Thank you
      singulars
      1. PO
      1. USGery
    3. CO@Gery i edited a little my answer, to explain why last query is not documented, and why i believe it will always work!
      singulars
      1. PO
      1. USfthiella
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

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