5 ms·

the max order_id is not always the latest id although it would seem to be so by design, however design is only as robust as the thousands of individuals buildi

• szundi 3y ago
  If you have an auto-increment and DO NOT have some logic around draft orders, max could be the one. Either way, you can max on the submission date then
  • eterm 3y ago
    Max on submission date doesn't return the corresponding order ID from that row, aggregations are applied separately. That's a classic pitfall that comes up all the time.
    • withinboredom 3y ago
      Sounds like a job for a windowing function.
      • williamdclt 3y ago
        Do you have an example?
        • deleted 3y ago
          [deleted]
        • 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 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 ago
              You 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 ago
                If 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 ago
              Oops, you are right
          • williamdclt 3y ago
            Im 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 ago
              It 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.
• dspillett 3y ago
  > although it would seem to be so by design, however design is only as robust as It only seems so if you assume auto-increment is used to populate the order_id and that it always increases with time. That latter assumption is quite unsafe: * Systems could have been merged with a bulk import of old orders into this one from elsewhere (assuming order_id is a surrogate key and there is a separate order code or such that is used to identify the orders externally). * In fact, a simple insert of several records in the same statement will not necessarily get auto-increment values in the order you expect (in practise they usually do - but the DB engines do not guarantee this, it is an accident of other factors in their design rather than a defined behaviour). * Because of optimisations for concurrency in the way auto-increment is handled, it is possible that long-running transactions could cause ordering discrepancies. In theory at least, in practise unless you've explicitly opted out of ACID-preserving locking semantics for those transactions I suspect these protections will stop this un-ordering happening by blocking the concurrency. This sort of issue is why you occasionally see unexpected gaps in auto-increment values. * I have seen an example where an incrementing signed-int ID was getting too close to MAXINT for comfort, and as a temporary measure ahead of changing that ID to be a longer type the increment was reset to restart at MININT and head back towards 0 from there! This was with a 16-bit integer (I'm old enough to have been around when it was common to use them to save space, where we generally default to 32-bit these days) but the same could happen to larger types.
• asielen 3y ago
  A nice feature of snowflake is the min_by/max_by functions. https://docs.snowflake.com/en/sql-reference/functions/min_by https://docs.snowflake.com/en/sql-reference/functions/min_by