StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POSQL query - Get rows only if value falls within range of "last n records" (record-specific)
text
Body
copied!<p>I have two tables, a Countries table and a Weather table. I would like to retrieve all of the names of countries where it has not rained within the last 15 days.</p> <p>The weather table has a column called "DayNum", which goes from 1 -> infinity and increases by 1 on each day, it is unique. This table also has a column called "Rain" which is just a bit boolean value of 0 or 1.</p> <p>Also, not all Countries were added on the same day, so the max DayNum will be different for each country.</p> <p>Examples of tables below (data is snipped for readability):</p> <p>Countries:</p> <pre><code> ID Name 1 USA 2 Cananda 3 Brazil </code></pre> <p>Weather</p> <pre><code> ID Country_id DayNum Rain 1 1 1 0 2 1 2 0 3 1 3 1 </code></pre> <p>Here is my current attempt at a query (been working on this for days):</p> <pre><code> SELECT countries.name, weather.daynum FROM countries INNER JOIN weather ON countries.id = weather.country_id GROUP BY countries.name HAVING weather.daynum > (MAX(weather.day_num) - 15) AND SUM(weather.rain) = 0; </code></pre> <p>I <em>think</em> this should work, but I'm having serious performance issues. The actual query I need to write deals with different data (same exact concept) and millions of rows. This query seems to get slower at an exponential rate.</p> <p>Can anyone offer any advice?</p> <p>Another idea I had was to somehow limit the JOIN to only grab the top 15 records (whilst ORDERing BY weather.day_num), but I Haven't found a way to do this within a JOIN (if it's even possible).</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