5 ms·
Hmm… my hunch is that this doesn’t do what you think it does. I expect the order by in the window function is effectively lost because max operated over the wh
by dent9876543 3y ago
Hmm… my hunch is that this doesn’t do what you think it does.
I expect the order by in the window function is effectively lost because max operated over the whole window. (And you happen to get the most recent, because in many implementations, order_id will be a sequence.)
But I might be wrong. And I might only now be learning that order by with max() and over substitutes how the “value” of the order_id is understood.
- withinboredom 3y agoYou aren't wrong. http://sqlfiddle.com/#!15/7eb3a/7 http://sqlfiddle.com/#!15/7eb3a/7 Here's a pretty simple/normalish way to handle the edge cases. This one (without distinct) is far more consistent (wall-clock-wise, doesn't depend on caches): http://sqlfiddle.com/#!15/7eb3a/9 http://sqlfiddle.com/#!15/7eb3a/9 Note that order 2 is after order 4 in the example schema.
- withinboredom 3y agoIf you just need customer id and order id (and nothing from the original orders table), you can simplify it further http://sqlfiddle.com/#!15/7eb3a/10 http://sqlfiddle.com/#!15/7eb3a/10
- billwashere 3y agoOops, you are right