I have table a where I have
Transaction_id (not unique, for one transaction it could be many string value for other status), status (for example A001,A002,A003), string value. I want to join to my main query only this string value which is assigned to the lowest number. I wrote query:
With data as
(select transaction_id, step_id, string_value
from global_data gd
inner join (Select transaction_id, min(substr(status,2,3)) as mini
from global_data gd1
group by deal_no) as ls
on ls.transaction_id=gd.transaction_id
and substr(gd.step_id,2,3)=ls.mini)
after this I join it to my global query but generation time increase 2 mins per 200 records so I need to optimize it. I believe that helped me optimize it.
2
You can just use a window function to filter the lowest status. Assuming you are using MySQL.
WITH data AS (
SELECT
transaction_id,
step_id,
string_value,
ROW_NUMBER() OVER(
PARTITION BY transaction_id
ORDER BY CAST(SUBSTR(status, 2, 3) AS SIGNED)
) AS rn
FROM global_data
)
SELECT * FROM data
WHERE rn = 1