select_first_row_in_one_to_many_relation.md

Dear guys,

This week I was facing this problem:

In agent zero we want to select the order along with the latest payment status and since order may have one or more payment status things was kinda tricky.

So first I write the query and select all payment status for the order like:

 SELECT so.order_nr, sop.id_sop, sop.status 
 FROM sales_order so 
 LEFT JOIN sales_order_payment sop ON so.id_so = sop.fk_so;

Which results:

order_nr	id_sop	status
AE1234	12	waiting approval
AE1234	14	on progress
AE1234	16	Successfull

But I need only the last result:

order_nr	id_sop	status
AE1234	16	Successfull

So first idea to solve this problem was using MAX() function like:

 SELECT so.order_nr, MAX(sop.id_sop), sop.status 
 FROM sales_order so 
 LEFT JOIN sales_order_payment sop ON so.id_so = sop.fk_so;

But I was getting invalid status while the id_sop was the max:

order_nr	id_sop	status
AE1234	16	on progress

The seconed idea to use subquery like:

 SELECT so.order_nr, sop.id_sop, sop.status 
 FROM sales_order so 
 LEFT JOIN (
  SELECT id_sop, status
  FROM sales_order_payment
  group by fk_sales_order
  order by id_sales_order_payment desc
 ) sop ON so.id_so = sop.fk_so;

that one worked but the joining with query has a huge impact in the performanse and the query processing time was huge

After a lot of search I found one nice soultion in stackoverflow

 SELECT so.order_nr, sop.id_sop, sop.status 
 FROM sales_order so 
 LEFT JOIN sales_order_payment sop ON so.id_so = sop.fk_so
 LEFT JOIN sales_order_payment sop2 ON sop2.fk_so = sop.fk_so AND sop.id_sop < sop2.id_sop
 WHERE sop2.id_sop IS NULL;

Thats solve the issue and performance was great, but it's kinda complex, right?

Lets try to analyze it:

So we did a self join to sales_order_payment but the joining conditions is sop2.fk_so = sop.fk_so AND sop.id_sop < sop2.id_sop which means: for each result get all sop2 rows that have the same order number and it's id is lower than the sop1 Id

 SELECT so.order_nr, sop.id_sop, sop.status, sop2.id_sop 
 FROM sales_order so 
 LEFT JOIN sales_order_payment sop ON so.id_so = sop.fk_so
 LEFT JOIN sales_order_payment sop2 ON sop2.fk_so = sop.fk_so AND sop.id_sop < sop2.id_sop;

Thats will give us an output like:

order_nr	id_sop	status	id_sop
AE1234	12	waiting approval	14
AE1234	12	waiting approval	16
AE1234	14	on progress	16
AE1234	16	Successfull	null

Then we add a where condition WHERE sop2.id_sop IS NULL to get only the greatest sop.id_sop value from the result set

 SELECT so.order_nr, sop.id_sop, sop.status 
 FROM sales_order so 
 LEFT JOIN sales_order_payment sop ON so.id_so = sop.fk_so
 LEFT JOIN sales_order_payment sop2 ON sop2.fk_so = sop.fk_so AND sop.id_sop < sop2.id_sop
 WHERE sop2.id_sop IS NULL;

and finally we got the correct resut:

order_nr	id_sop	status
AE1234	16	Successfull