Joining first row of other table only

[Dawid_Nawrot (Dawid)]

Please imagine following scenario:

customers

id | name | active

orders

id | value | status | customer_id | created_at

I am trying to avoid creating a new view or PDT, in order to get orders joined to the patients but only first row.

I added dimension called customer_order_no with the following SQL:

ROW_NUMBER() OVER(PARTITION BY orders.customer_id ORDER BY orders.created_at ASC

Then in the model file I created a join:

  join: orders {
    fields: []
    type: left_outer
    sql_on: ${customer.customer_id} = ${orders.customer_id} AND ${orders.customer_order_no} = 1  ;;
    relationship: one_to_one
  }

Of course then I realised I can’t put window functions in JOINs or WHERE clauses. I would want to avoid another PDT, so I’m just throwing it out here…

Anybody has a nice way of achieving it?

[bens (Ben Silverstein)]

Hey Dawid,

Just a suggestion, but what if you:

-take your order_id field, and create a minimum-type measure (min_order_id)
-create a yesno type measure where order_id = min_order_id for your users to filter on. This should restrict the query accordingly, and also brings you flexibility to grab the first order not just for a customer, but also for a store/category/any other way you’d slice and dice, making it more robust than an NDT/PDT

Edit: Wanted to mention that my assumption from how you’ve described the scenario is that you’d like analytics on the first orders placed by clients. Is that right?

[Dawid_Nawrot (Dawid)]

Your assumption is correct… and since order numbers are incremental the minimum measure could work. I will test it out tomorrow. Cheers @bens