simple MAX SQL returns wrong result (!)

[Peter Primrose]

Can't figure it out why the MAX return-result is incorrect.

here's my table result (composed of 2 tables)



I would like to get the highest bidder, for this I'm using the MAX:

    
SELECT MAX(CB.offer_amount) AS High_bid, E.legal_name, case_ID, e.entity_id, CB.status_2 
 
FROM 
entity AS E, case_buyer as CB
 
WHERE
CB.case_ID=700017
AND E.entity_id = CB.entity_ID
GROUP BY case_ID  
 

but the result is:

    
High_bid...legal_name....case_ID....entity_id...status_2  
745..........Dell........700017......1............764 
 

Why? the highest bidder is Tony Soprano not DELL

[Amit M Tank]

because 745 is greater than other numbers of the coulmn. seems there is something fundamentally wrong in your question.

[Peter Primrose]

seems there is something fundamentally wrong in your answer :-)

if 745 is the highest bid why the result for *legal_name* is dell and not * tony soprano* and how do I correct this?

[Campbell Ritchie]

ORDER BY

?

I think you are choosing the highest bid for each of the bidders, not the highest bid of all.

[Jeanne Boyarsky]

Peter,
I think you want something more like this:

SELECT CB.offer_amount AS High_bid, E.legal_name, case_ID, e.entity_id, CB.status_2 
 
FROM 
entity AS E, case_buyer as CB
 
WHERE
CB.case_ID=700017
AND E.entity_id = CB.entity_ID
and cb.offer_amount = (select MAX(offer_amount) from case_buyer)
GROUP BY case_ID  

The max check is in the where clause so it hones in on the record you are looking for. (I didn't run this, so the syntax might be a bit off.)

[Peter Primrose]

yes, you are correct. the 'MAX' should be on the 'WHERE'.

THANK YOU!