-5

Given the following example datamodels/tables: (specified in pseudo-Ruby/Rails for brevity's sake)

class User
  has_many :orders
end

class Order
  has_and_belongs_to_many :products
  ordered_at :timestamp
end

class Product
  name :string
  price :decimal
end

(and each of these tables also has a unique 'id' identifier field that is its primary key, and a joins table connecting Order and Product also exists)

I would like to select all users, for which e.g. their oldest Order (with the earliest ordered_at), contains more than n products.

How can this be done?

What I've tried so far: I tried using a JOIN, but then we end up looking at all orders rather than only the oldest. I've also tried to use DISTINCT/DISTINCT ON but I have no idea how to combine this properly with (a) selecting the users and (b) doing a check for the count of all related products.

I am using Postgres, but if a DB-agnostic SQL answer exists, that would be much appreciated as well.

Qqwy
  • 5,214
  • 5
  • 42
  • 83
  • 4
    If you ask a question about SQL, then it would help if the SQL guys can understand the question. And not all of them may speak "pseudo-Ruby/Rails". So please translate that to `CREATE TABLE` statements. Also add some sample data as `INSERT INTO` statements and the desired result with that sample data in tabular text form. – sticky bit May 24 '19 at 21:37
  • (Obviously--) This is a faq. Before considering posting please always google any error message & many clear, concise & precise phrasings of your question/problem/goal, with & without your particular strings/names; read many answers. If you post a question, use one phrasing as title. See [ask] & the voting arrow mouseover texts. Please in code questions give a [mcve]--cut & paste & runnable code; example input with desired & actual output (including verbatim error messages); clear specification & explanation. PS There is even a tag for questions involving this faq [tag:greatest-n-per-group]. – philipxy May 24 '19 at 21:56
  • Possible duplicate of [Select first row in each GROUP BY group?](https://stackoverflow.com/questions/3800551/select-first-row-in-each-group-by-group) – philipxy May 24 '19 at 22:02
  • I just found that duplicate link googling 'site:stackoverflow.com sql select all users, for which e.g. their oldest Order'. That is not even clear, it's just cut & pasted from your message but it is already clear enough even with your particular business & DB strings. – philipxy May 24 '19 at 22:04
  • 2
    It is not clear what your tables actually look like in Postgres. – Gordon Linoff May 24 '19 at 22:50
  • https://stackoverflow.com/questions/tagged/greatest-n-per-group+postgresql –  May 25 '19 at 04:09

1 Answers1

1

In Postgres, I would phrase this using distinct on. The basic idea would be:

select o.*
from (select distinct on (o.user_id) o.user_id, o.order_id, o.ordered_at, count(*) as num_products
      from orders o join
           orderproducts op
           on op.order_id = o.order_id
      group by o.user_id, o.order_id, o.ordered_at
      order by o.user_id, o.ordered_at asc
     ) o
where num_products >= n
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786