Select one row from detail records based on condition.

I often come across the issue to return one row from detail table like invoice items or order details for one customer for specific condition.
i.e. show me the max order product for each customer and order

that kind of query could be solved using self join, grouping and having clause

we have three tables, 1. customer 2. Order 3. OrderItem

the query look like

select c.customer_id, oi.order_id, oi.product_cost from order_item oi 
inner join order_item oii on oi.order_id = oii.order_id
inner join customer c on c.customer_id = oi.customer_id
group by c.customer_id, oi.order_id, oi.product_cost
having oi.product_cost >= max(oii.product_cost)

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s