StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>This is a way I used to cope with such problems:</p> <pre><code>SELECT distinct t3.id FROM (SELECT t1.id, t1.day, MIN(t2.day) nextday FROM pp t1 JOIN pp t2 ON t1.id=t2.id AND t1.day<t2.day GROUP BY t1.id, t1.day HAVING nextday-t1.day >50) t3 </code></pre> <p>(<strong>EDIT</strong> this version is slightly better) This finds all the IDs for which there is a delta > 50. (I assumed that this is what you're after)</p> <p>To see it working: <a href="http://sqlfiddle.com/#!2/87955/3" rel="nofollow">SQL fiddle</a></p> <p>To find the max deltas:</p> <pre><code>SELECT t3.id, MAX(t3.nextday-t3.day) FROM (SELECT t1.id, t1.day, MIN(t2.day) nextday FROM pp t1 JOIN pp t2 ON t1.id=t2.id AND t1.day<t2.day GROUP BY t1.id, t1.day) t3 GROUP BY t3.id </code></pre> <p>The logic behind is to find the "next" item, whatever that means. As this is an ordered attribute, the next item can be defined as having the lowest value among those rows that have the value larger than the one examined... Then you join the "next" values to the original values, conpute the delta, and return only those that are applicable. If you need the other columns too, just do a JOIN on the outer select to the original table.</p> <p>I'm not sure if this is the best solution regarding perfirmance, but I only wrote queries for one-off reports, with which I could afford the query to run for a while.</p> <p>There is one semantic error though, that can arise: if somebody was present on the 1st, 2nd and 3rd days, but <strong>never after</strong>, this does not find the absence. To overcome this, you could add a special row with <code>UNION</code>ing a select to the tables specifying tomorrow's day count for all <code>ID</code>s, but that would make this query disgusting enough not to try writing it down...</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