StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>I don't have Oracle to test it, but the most efficient solution is to use analytic queries. It should look something like this:</p> <pre><code>SELECT DISTINCT UserId , MaxValue FROM ( SELECT UserId , FIRST (Value) Over ( PARTITION BY UserId ORDER BY Date DESC ) MaxValue FROM SomeTable ) </code></pre> <p>I suspect that you can get rid of the outer query and put distinct on the inner, but I'm not sure. In the meantime I know this one works.</p> <p>If you want to learn about analytic queries, I'd suggest reading <a href="http://www.orafaq.com/node/55" rel="nofollow noreferrer">http://www.orafaq.com/node/55</a> and <strike><a href="http://www.akadia.com/services/ora_analytic_functions.html" rel="nofollow noreferrer">http://www.akadia.com/services/ora_analytic_functions.html</a></strike>. Here is the short summary.</p> <p>Under the hood analytic queries sort the whole dataset, then process it sequentially. As you process it you partition the dataset according to certain criteria, and then for each row looks at some window (defaults to the first value in the partition to the current row - that default is also the most efficient) and can compute values using a number of analytic functions (the list of which is very similar to the aggregate functions).</p> <p>In this case here is what the inner query does. The whole dataset is sorted by UserId then Date DESC. Then it processes it in one pass. For each row you return the UserId and the first Date seen for that UserId (since dates are sorted DESC, that's the max date). This gives you your answer with duplicated rows. Then the outer DISTINCT squashes duplicates.</p> <p>This is not a particularly spectacular example of analytic queries. For a much bigger win consider taking a table of financial receipts and calculating for each user and receipt, a running total of what they paid. Analytic queries solve that efficiently. Other solutions are less efficient. Which is why they are part of the 2003 SQL standard. (Unfortunately Postgres doesn't have them yet. Grrr...)</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