• Post Reply Bookmark Topic Watch Topic
  • New Topic

simple MAX SQL returns wrong result (!)

 
Peter Primrose
Ranch Hand
Posts: 755
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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:


but the result is:



Why? the highest bidder is Tony Soprano not DELL
 
Amit M Tank
Ranch Hand
Posts: 257
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
because 745 is greater than other numbers of the coulmn. seems there is something fundamentally wrong in your question.
 
Peter Primrose
Ranch Hand
Posts: 755
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Marshal
Posts: 53165
124
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
ORDER BY

?

I think you are choosing the highest bid for each of the bidders, not the highest bid of all.
 
Jeanne Boyarsky
author & internet detective
Sheriff
Posts: 36031
432
Eclipse IDE Java VI Editor
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Peter,
I think you want something more like this:


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
Ranch Hand
Posts: 755
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
yes, you are correct. the 'MAX' should be on the 'WHERE'.

THANK YOU!
 
Gravity is a harsh mistress. But this tiny ad is pretty easy to deal with:
the new thread boost feature: great for the advertiser and smooth for the coderanch user
https://coderanch.com/t/674455/Thread-Boost-feature
  • Post Reply Bookmark Topic Watch Topic
  • New Topic
Boost this thread!