Databases
StackOverflow2013
Postsdbo
POhow to select a field from a row that is chosen by group-by in mySQL
Body
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POhow to select a field from a row that is chosen by group-by in mySQL
    text
    copied!<p>I have a GIANT MYSQL database that contains 100,000,000 records.</p> <p>Simplified, three of my columns are <strong>ID</strong>, <strong>date</strong> and <strong>time</strong>, i have several indexes over id and date ( <strong>ID , DATE , ID&amp;DATE</strong> ) so no performance problem on join </p> <pre><code> select id, max(date) as last_record from mytable where date &lt; "2010-01-02" mytable /--------------------------------\ | ID | date | time | |--------+--------------+--------| | 1 | 2009-01-01 | 15 | |--------+--------------+--------| | 1 | 2009-01-03 | 14 | &lt;-- This |--------+--------------+--------| | 2 | 2009-01-01 | 18 | |--------+--------------+--------| | 2 | 2009-01-02 | 12 | |--------+--------------+--------| | 2 | 2009-01-02 | 15 | &lt;-- and This \--------+--------------+--------/ </code></pre> <p>That results like:</p> <pre><code> /-----------------------\ | ID | last_record | |--------+--------------| | 1 | 2009-01-03 | |--------+--------------| | 2 | 2009-01-02 | \--------+--------------/ </code></pre> <p><strong>IMPROVED QUESTION:</strong> Now i want this query to tell me about the max<strong>time</strong> of the records that are chosen by group by, like:</p> <pre><code> /--------------------------------\ | ID | last_record | time | |--------+--------------+--------| | 1 | 2009-01-03 | 14 | |--------+--------------+--------| | 2 | 2009-01-02 | 15 | \--------+--------------+--------/ </code></pre> <p>I need some idea to do this !</p> <p><strong>EDIT:(MORE INFORMATION)</strong> i want to know last record and the time of that record for all of my Ids</p>
 

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