This week's book giveaway is in the Other Languages forum.
We're giving away four copies of Rust Web Development and have Bastian Gruber on-line!
See this thread for details.
Win a copy of Rust Web Development this week in the Other Languages forum!
  • Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Tim Cooke
  • Campbell Ritchie
  • Ron McLeod
  • Liutauras Vilda
  • Jeanne Boyarsky
Sheriffs:
  • Junilu Lacar
  • Rob Spoor
  • Paul Clapham
Saloon Keepers:
  • Tim Holloway
  • Tim Moores
  • Jesse Silverman
  • Stephan van Hulst
  • Carey Brown
Bartenders:
  • Al Hobbs
  • Piet Souris
  • Frits Walraven

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

 
Greenhorn
Posts: 22
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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:
 
author & internet detective
Posts: 40913
840
Eclipse IDE VI Editor Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
reply
    Bookmark Topic Watch Topic
  • New Topic