Databases
StackOverflow2013
Postsdbo
PO
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PO
    primarykey
    data
    text
    <p>This is an example of the frequently-asked "<a href="https://stackoverflow.com/questions/tagged/greatest-n-per-group">greatest-n-per-group</a>" question that we see every week on StackOverflow. Follow that tag to see other similar solutions.</p> <pre><code>SELECT s.*, f1.* FROM stock s INNER JOIN stockfile f1 ON (s.stockfileid = f1.stockfileid) LEFT OUTER JOIN stockfile f2 ON (s.stockfileid = f2.stockfileid AND f1.date &lt; f2.date) WHERE f2.stockfileid IS NULL; </code></pre> <p>If there are multiple rows in <code>stockfile</code> that have the max date, you'll get them both in the result set. To resolve this, you'd have to add some tie-breaker conditions into the join on <code>f2</code>.</p> <hr> <p>Thanks for adding the <code>CREATE TABLE</code> info. That's very helpful when you're asking SQL questions.</p> <p>I see from the <code>AUTO_INCREMENT</code> table options that you have 315k rows in <code>stock</code> and only 265 rows in <code>stockfile</code>. Your <code>stockfile</code> table is the parent in the relationship, and the <code>stock</code> table is the child, with a column <code>stockfileid</code> that references the primary key of <code>stockfile</code>. </p> <p>So your original question was misleading. You want the latest row from <code>stock</code>, not the latest row from <code>stockfile</code>.</p> <pre><code>SELECT f.*, s1.* FROM stockfile f INNER JOIN stock s1 ON (f.stockfileid = s1.stockfileid) LEFT OUTER JOIN stock s2 ON (f.stockfileid = s2.stockfileid AND (s1.touchdate &lt; s2.touchdate OR s1.touchdate = s2.touchdate AND s1.stockid &lt; s2.stockid)) WHERE s2.stockid IS NULL; </code></pre> <p>I'm assuming you want "latest" to be relative to <code>touchdate</code>, so if you want to use <code>creationdate</code> instead, you can do the edit.</p> <p>I've added a term to the join so that it resolves ties. I know you said the dates are "practically unique" but as the saying goes, "<a href="http://blogs.msdn.com/larryosterman/archive/2004/03/30/104165.aspx" rel="nofollow noreferrer">one in a million is next Tuesday</a>."</p> <hr> <p>Okay, I think I understand what you're trying to do now. You want the most recent row per <code>sku</code>, but the <code>date</code> by which to compare them is in the referenced table <code>stockfile</code>. </p> <pre><code>SELECT s1.*, f1.* FROM stock s1 JOIN stockfile f1 ON (s1.stockfileid = f1.stockfileid) LEFT OUTER JOIN (stock s2 JOIN stockfile f2 ON (s2.stockfileid = f2.stockfileid)) ON (s1.sku = s2.sku AND (f1.date &lt; f2.date OR f1.date = f2.date AND f1.stockfileid &lt; f2.stockfileid)) WHERE s2.sku IS NULL; </code></pre> <p>This does a self-join of <code>stock</code> to itself, looking for a row with the same <code>sku</code> and a more recent <code>date</code>. When none is found, then <code>s1</code> contains the most recent row for its <code>sku</code>. And each instance of <code>stock</code> has to join to its <code>stockfile</code> to get the <code>date</code>.</p> <hr> <p>Re comment about optimization: It's hard for me to test because I don't have tables populated with data matching yours, but I'd guess you should have the following indexes:</p> <pre><code>CREATE INDEX stock_sku ON stock(sku); CREATE INDEX stock_stockfileid ON stock(stockfileid); CREATE INDEX stockfile_date ON stockfile(date); </code></pre> <p>I'd suggest using <code>EXPLAIN</code> to analyze the query without the indexes, and then create one index at a time and re-analyze with <code>EXPLAIN</code> to see which one gives the most direct benefit.</p>
    singulars
    1. This table or related slice is empty.
    1. POSelect newest entry from a joined MySQL table
      singulars
      1. PTQuestion
    1. PTAnswer
    1. USCommunity
    1. USBill Karwin
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. POSelect newest entry from a joined MySQL table
      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. VTAcceptedByOriginator
    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. VTUpMod
    1. COI know, I've actually solved a similar problem thanks to stackoverflow. But your query still gives me 315.000 results :/
      singulars
      1. PO
      1. USskerit
    2. COThen I would guess there are a lot of ties for the max date per stock.
      singulars
      1. PO
      1. USBill Karwin
    3. COA lot of ties? The "date" field is practically unique in the stockfile table. Per date there can be about 100 records in the stock table
      singulars
      1. PO
      1. USskerit
 

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