CODEWITHSUNDEEP
mysqlsql
Dylan Fontaine
Dylan Fontaine

Reputation: 15

MySQL - Trying to sort by two different columns from different tables

Hey I'm having a bit of trouble coming up with the SQL for the action I would like to perform.

I have a table listings which has a start_price field and an active field. I only want to pull up listings that are active(1).

I want to order these listings by the current price. I do not have a current price field in either my listings or bids table. So if a listing_id is in the bids table I want to assign the largest value in the amount field that corresponds to the matching listing_id from the bids table, to the current price, otherwise the start_price from listings should be assigned to the current price.

This is what I have come up with so far. I have been mucking around with different things and cannot come up with the correct syntax.

SELECT DISTINCT * FROM listings l  LEFT JOIN bids b ON l.id = b.listing_id WHERE l.active = 1 ORDER BY l.start_price

Upvotes: 0

Views: 54

Answers (3)

Gervs
Gervs

Reputation: 1397

You'll need a GROUP BY and an aggrate function, in this case MAX():

SELECT
     l.*, /* columnlist here */
     COALESCE(MAX(b.current_price), l.start_price) price
FROM
     listings l
LEFT JOIN bids b
     ON l.id = b.listing_id
WHERE l.active = 1
GROUP BY l.listing_id 
ORDER BY price

When there are no bids on a listing the MAX functions returns NULL so the second value in de COALESCE will be selected.

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269513

There are multiple ways that you can do the calculation. I think the key idea for you is that you can use a column alias in the order by clause.

The following does the calculation using a correlated subquery, then using coalesce() if there is no match:

select l.*,
       coalesce( (select max(b.price)
                  from bids b
                  where b.listing_id = l.listing_id
                 ), l.start_price
               ) as current_price
where l.active = 1
from listings l
order by current_price;

Note: I am a little concerned about using the maximum price from the bids, rather than the most recent price. Is it possible that bids could be withdrawn, but the row remains in the table?

Upvotes: 1

CrApHeR
CrApHeR

Reputation: 2655

I think this is going to solve the problem.

SELECT
  listing_id,
  COALESCE(current_price, start_price) current_price
FROM
  (SELECT
    l.id, b.listing_id, l.start_price, MAX(b.Amount) current_price
  FROM
    listing l
    LEFT JOIN bids b ON l.id = b.listing_id
  WHERE
    l.active = 1
  GROUP BY
    l.id, b.listing_id, l.start_price) Data
ORDER BY
  COALESCE(current_price, start_price)

In the subquery I am getting the start_price and the maximum price of each item (one row per item).

In the main query I am using that information to define if I want to use the maximum value or if it is NULL the start_price.

Sorry if there is any mistake or mistype but I don't have a mysql server in here to test it.

Hope this helps

Upvotes: 0

Related Questions