How to join the newest rows from a table?

Question

I frequently run into problems of this form and haven't found a good solution yet:

Assume we have two database tables representing an e-commerce system.

userData (userId, name, ...)
orderData (orderId, userId, orderType, createDate, ...)

For all users in the system, select their user information, their most recent order information with type = '1', and their most recent order information with type = '2'. I want to do this in one query. Here is an example result:

(userId, name, ..., orderId1, orderType1, createDate1, ..., orderId2, orderType2, createDate2, ...)
(101, 'Bob', ..., 472, '1', '4/25/2008', ..., 382, '2', '3/2/2008', ...)

Answer 1

This should work, you'll have to adjust the table / column names:

select ud.name,
       order1.order_id,
       order1.order_type,
       order1.create_date,
       order2.order_id,
       order2.order_type,
       order2.create_date
  from user_data ud,
       order_data order1,
       order_data order2
 where ud.user_id = order1.user_id
   and ud.user_id = order2.user_id
   and order1.order_id = (select max(order_id)
                            from order_data od1
                           where od1.user_id = ud.user_id
                             and od1.order_type = 'Type1')
   and order2.order_id = (select max(order_id)
                             from order_data od2
                            where od2.user_id = ud.user_id
                              and od2.order_type = 'Type2')

Denormalizing your data might also be a good idea. This type of thing will be fairly expensive to do. So you might add a last_order_date to your userData.