3 ms·
select distinct customer_id, max(order_id) over (partition by customer_id order by created_date desc) FROM orders http://sqlfiddle.com/#!15/51df39/2 http://sql
by billwashere 3y ago
select distinct customer_id, max(order_id)
over (partition by customer_id order by created_date desc)
FROM orders
http://sqlfiddle.com/#!15/51df39/2 http://sqlfiddle.com/#!15/51df39/2
- dent9876543 3y agoHmm… 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
- williamdclt 3y agoIm very ignorant of partition by, but it doesn’t look like it works? http://sqlfiddle.com/#!15/696cb/1 http://sqlfiddle.com/#!15/696cb/1
- withinboredom 3y agoIt doesn't. http://sqlfiddle.com/#!15/7eb3a/7 http://sqlfiddle.com/#!15/7eb3a/7 is a proper implementation using windowing functions to get the first something.