Getting a value from the same row as the row the min() value comes from

ApplowPi 0
Viewed 48

Here's my query.

There's a complicated system, which requires this query. I'd like to know, how do I get the currency value from the same row the min(j.price) value comes from?

I know the query is a little messy but I'm essentially asking how to get a value from the same row that the value from a min() function comes from?

SELECT 
    count(p.name), p.name, p.image_url, p.type, p.number, p.productCode, p.productType, 
    p.material, p.blueprint, min(j.price) as price
FROM products AS p
left join (
    SELECT 
        p.token_id, p.name, p.image_url, p.type, p.number, p.productCode, 
        p.productType, p.material, p.blueprint, o2.order_id, o2.currency, 
        o2.eth_price, o2.price, o2.usd_price, o2.quantity, o2.isOurs, 
        o2.order_status, o2.updated_timestamp, ROW_NUMBER()
    OVER (PARTITION BY order_status ORDER BY usd_price ASC) rn
    FROM products AS p
    left JOIN (
        SELECT token_id, MAX(updated_timestamp) AS Latest_order
        FROM orders
        GROUP BY token_id
    ) o1 ON p.token_id = o1.token_id
    left JOIN orders o2 ON o1.token_id = o2.token_id AND o1.Latest_order = o2.updated_timestamp
    where o2.order_status = 'active'
    ORDER BY name asc
) j on p.token_id = j.token_id
group by p.name, p.image_url, p.type, p.number, p.productCode, p.productType, p.material, p.blueprint
order by name

For @FanoFN enter image description here

mysql
1 Answers

Perhaps SUBSTRING_INDEX() and GROUP_CONCAT() can help. So, instead of:

... , min(j.price) as price

How about something like this:

..., 
  SUBSTRING_INDEX(GROUP_CONCAT(j.price ORDER BY j.price ASC),',',1) AS price, 
  SUBSTRING_INDEX(GROUP_CONCAT(j.currency ORDER BY j.price ASC),',',1) AS currency

It is quite long but it should work.

Refer this demo fiddle

FanoFN0
Related