CODEWITHSUNDEEP
mysqlsqldatabase
Wojtek Wencel
Wojtek Wencel

Reputation: 2117

How to join tables using the highest value?

I'm joining two tables based on id_order and it's one to many relation. My query looks like this:

SELECT
ps_order_detail.product_name,
ps_order_history.id_order_state
FROM ps_order_detail JOIN
ps_order_history using (id_order)

The problem is that it returns all of the id_order_state values, and I want only the highest one. I tried doing max(ps_order_history.id_order_state) but it returns only one record with the highest value, and I want to have the highest value for each id_order. How can I do it?

Upvotes: 0

Views: 56

Answers (2)

Sandeep Kumar Narware
Sandeep Kumar Narware

Reputation: 250

You need to use aggregate function max with a group by clause. I am not sure which flavor of SQL server you are using. For MS SQL Server following query will do what you want.

SELECT product_name, MAX(id_order_state) 
FROM ps_order_detail 
GROUP BY product_name

Upvotes: 3

xQbert
xQbert

Reputation: 35323

One approach is to generate a subset of the max ID_ORDER_STATE per order and add it to the joins.

SELECT ps_order_detail.product_name
     , ps_order_history.id_order_state
FROM ps_order_detail 
INNER JOIN ps_order_history using (id_order)
INNER JOIN (SELECT max(ID_ORDER_STATE) MOS, ID_ORDER 
            FROM ps_order_history
            GROUP BY ID_ORDER) Z
 on Z.MOS      = ps_order_history.id_order_state
and Z.ID_ORDER = ps_order_history.id_order

This approach allows you to return the other data from history related to the max record; but it's overkill if all you need is the max order state for each product name

Other approaches involve using cross apply or analytical functions but mySQL doesn't support those approaches.

I suppose you could use an exists and correlate subquery as well... but I find the above approach clean to read.

Upvotes: 1

Related Questions