CODEWITHSUNDEEP
sqljoin
user1022585
user1022585

Reputation: 13641

sql order with join

i have this sql query:

SELECT b.topbid, b.topdate, a.* 
FROM auction_items a 
LEFT JOIN 
   (SELECT itemID, MAX(bid) as topbid, 
   MAX(date) as topdate FROM auction_bids GROUP BY itemID ) b 
ON a.id = b.itemID 
ORDER BY b.topdate DESC, a.date DESC LIMIT 20

It's not ordering how i'd like it to. I want it to order by merging b.topdate and a.date.

What is wrong?

Upvotes: 1

Views: 146

Answers (3)

DeanG
DeanG

Reputation: 647

Try Selecting one or the other using a CASE statement, then order by that value instead :

SELECT b.topbid, b.topdate, a.* , CASE WHEN b.topdate > a.date then b.topdate ELSE a.date     END AS oDate
FROM auction_items a 
LEFT JOIN 
   (SELECT itemID, MAX(bid) as topbid, 
   MAX(date) as topdate FROM auction_bids GROUP BY itemID ) b 
ON a.id = b.itemID 
ORDER BY oDate DESC LIMIT 20

Upvotes: 0

northpole
northpole

Reputation: 10346

do you mean order by concatenating the two values? if yes, try the following:

SELECT b.topbid, b.topdate, a.* 
FROM auction_items a 
LEFT JOIN 
   (SELECT itemID, MAX(bid) as topbid, 
   MAX(date) as topdate FROM auction_bids GROUP BY itemID ) b 
ON a.id = b.itemID 
ORDER BY b.topdate || a.date DESC LIMIT 20

I am not sure what RDBMS you are in, but Oracle concatenation is pipes ||

EDIT: if using MySQL use the CONCAT function:

SELECT b.topbid, b.topdate, a.* 
    FROM auction_items a 
    LEFT JOIN 
       (SELECT itemID, MAX(bid) as topbid, 
       MAX(date) as topdate FROM auction_bids GROUP BY itemID ) b 
    ON a.id = b.itemID 
    ORDER BY CONCAT(b.topdate,a.date) DESC LIMIT 20

Upvotes: 1

Ryan Guill
Ryan Guill

Reputation: 13896

You cannot have it order by both b.topdate and a.date, because they could both have a value. If you can say that when they both have a value that you should use one over the other (for instance topdate over date), you can do it like this:

SELECT b.topbid, b.topdate, a.* 
FROM auction_items a 
LEFT JOIN 
   (SELECT itemID, MAX(bid) as topbid, 
   MAX(date) as topdate FROM auction_bids GROUP BY itemID ) b 
ON a.id = b.itemID 
ORDER BY COALESCE(b.topdate, a.date) DESC LIMIT 20

Note the order by. If its the other way around, make it COALESCE(a.date, b.topdate)

Upvotes: 0

Related Questions