I have three tables for orders of toy cars/planes:
cars planes
========================= =======================
id manufacturer model id manufacturer model
1 Toyota Trueno 1 Boeing 747
2 Toyota Corolla 2 Airbus A300
3 Mazda RX7 FC 3 Airbus A330
4 Subaru WRX
5 Nissan Silvia
6 Nissan Silvia
7 Mazda RX7 FD
orders
======
id car_id plane_id
1 1 NULL
2 2 NULL
3 3 NULL
4 4 NULL
5 NULL 1
6 NULL 2
7 5 NULL
8 NULL 3
9 6 NULL
10 7 NULL
How can I write an Ecto query that returns the N most recent orders (in terms of order id) without duplicate CAR manufacturers? (Orders with duplicate plane manufacturers are allowed.)
E.g. in the above tables, there are two Nissan car orders and two Mazda car orders. So, regardless of N, the older Nissan car order (order id=7) and the older Mazda car order (order_id=3) shouldn’t appear in the results. To illustrate, when N=5, the returned results should be the orders with IDs 10,9,8,6,5. When N=10, only the eight orders 10,9,8,6,5,4,2,1 should be returned.
By the way, the numbers of car or plane manufacturers here are potentially unbounded. We do not assume any known list of manufacturers in compile time.