StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>EDIT: My previous answer was nonsense. This is now a complete rewrite</p> <p>This is actually a problem which has bugged me throughout my SQL life. The solution I'm going to give you is messy as hell but it works and I'd appreciate anyone either saying "yes this is messy as hell but it's the only way to do it" or say "no, do this...".</p> <p>I think the unease comes from joining two dates. The way it happens here it's not an issue as they will be an exact match (they have exactly the same root data) but it still feels wrong...</p> <p>Anyway, breaking this down, you need to do this in two stages. </p> <p>1) The first is to return a results set [AID], [earliest CreationTime] giving you the earliest creationtime for each AID.</p> <p>2) You can then use latestCreationTime to pull the CID you want.</p> <p>So for part (1), I'd personally create a view to do it just to keep things neat. It allows you to test this part and get it working before you merge it with the other stuff.</p> <pre><code>create view LatestCreationTimes as select b.AID, max(c.CreationTime) LatestCreationTime from TableB b, TableC c where b.CID = c.CID group by b.AID </code></pre> <p>Note, we've not taken into account the status at this point.</p> <p>You then need to join that to TableA (to get the status) and TableB and TableC (to get the CID). You need to do all the obvious links (AID, CID) and also join the LatestCreationTime column in the view to the CreationTime column in TableC. Don't also forget to join the view on AID otherwise where two records have been created at the same time for different A records you'll get issues.</p> <pre><code>select A.AID, C.CID from TableA a, TableB b, TableC c, LatestCreationTimes lct where a.AID = b.AID and b.CID = c.CID and a.AID = lct.AID and c.CreationTime = lct.LatestCreationTime and a.STATUS = 'OK' </code></pre> <p>I'm certain that works - I've tested it, tweaked data, retested it and it behaves. At least it does what I believe it's meant to do.</p> <p>It doesn't however deal with the possibility of two identical CreationTimes in table C for the same record. I'm guessing that this shouldn't happen however unless you've written sometime that absolutely constrains it it needs to be accounted for.</p> <p>To do this I need to make an assumption about which one you'd prefer. In this case I'm going to say that if there are two CIDs which match, you'd rather have the higher one (it's most likely more up to date).</p> <pre><code>select A.AID, max(C.CID) CID from TableA a, TableB b, TableC c, LatestCreationTimes lct where a.AID = b.AID and b.CID = c.CID and c.CreationTime = lct.LatestCreationTime and a.STATUS = 'OK' group by A.AID </code></pre> <p>And that, I believe should work for you. If you want it as one query rather than with the view then:</p> <pre><code>select A.AID, max(C.CID) CID from TableA a, TableB b, TableC c, (select b.AID, max(c.CreationTime) LatestCreationTime from TableB b, TableC c where b.CID = c.CID group by b.AID) lct where a.AID = b.AID and b.CID = c.CID and c.CreationTime = lct.LatestCreationTime and a.STATUS = 'OK' group by A.AID </code></pre> <p>(I've just embedded the view in the query, otherwise the principal is exactly the same).</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