This week's book giveaway is in the Agile and Other Processes forum.
We're giving away four copies of The Little Book of Impediments (e-book only) and have Tom Perry on-line!
See this thread for details.
Win a copy of The Little Book of Impediments (e-book only) this week in the Agile and Other Processes forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Question on query - select one row with max value in one field

 
Steven Jiang
Greenhorn
Posts: 22
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Suppose I have Table Stock (Id, Name, userId, price)

To select a row with highest price given a userId, what the query should be???

I know a way, which I thought a bit complex, anybody here has better idea?

Thanks

My query is:
 
Jeanne Boyarsky
author & internet detective
Marshal
Posts: 35279
384
Eclipse IDE Java VI Editor
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Steven,
Why do you have id in the main query? Doesn't that make it distinct? Also, note that you could get more than one record back if there are identical prices.

I would use the code you wrote because it isn't database specific. Another approach is the following. Note that your database needs to support returning only one row and you should check the performance is equivalent through your database explain plan.
 
Steven Jiang
Greenhorn
Posts: 22
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Jeanne Boyarsky wrote:Steven,
Why do you have id in the main query? Doesn't that make it distinct? Also, note that you could get more than one record back if there are identical prices.

I would use the code you wrote because it isn't database specific. Another approach is the following. Note that your database needs to support returning only one row and you should check the performance is equivalent through your database explain plan.


Sorry Jeanne, I typed wrong, the first id=? should be userId=?, no wonder I only have your response so far, making people confused

Thanks for your second option, that is a good one if database support it. or use HQL.

Steve
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic