Hacker News Reader

Comment by continuational | Hacker News Reader

Quite often what you want isn't an arbitrary row (for which exists is correct), but a specific one.

In that case, PostgreSQL has a feature where you can do this:

    select distinct on (customer_id) customer_id, order_id
    from orders
    order by customer_id, created_date desc

And get all customer IDs with their latest order ID.

View on HN

Is that different/better/worse than?

  select customer_id, max(order_id)
  from orders
  group by customer_id
  order by customer_id

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 building on the system :)

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

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.

Sounds like a job for a windowing function.

Do you have an example?

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

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.

You aren't wrong.

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

Note that order 2 is after order 4 in the example schema.

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

Oops, you are right

Im very ignorant of partition by, but it doesn’t look like it works? http://sqlfiddle.com/#!15/696cb/1

It doesn't. http://sqlfiddle.com/#!15/7eb3a/7 is a proper implementation using windowing functions to get the first something.

> 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.

A nice feature of snowflake is the min_by/max_by functions.

https://docs.snowflake.com/en/sql-reference/functions/min_by

Reply on news.ycombinator.com