Looker Community

Joining first row of other table only

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?

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?

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