This week's book giveaways are in the Refactoring and Agile forums.
We're giving away four copies each of Re-engineering Legacy Software and Docker in Action and have the authors on-line!
See this thread and this one for details.
Win a copy of Re-engineering Legacy Software this week in the Refactoring forum
or Docker in Action in the Agile forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Inner SQL

 
Viswa Sethu
Ranch Hand
Posts: 31
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I have a table testable

col1 col2 col3
10 A Name1
20 A Name1
25 B Name1
30 A Name1
30 A Name2
40 A Name2
45 A Name3
50 A Name3

and so on...

I want to select the rows which has max of col1 and with col2 value as 'A'

The result must be like :
30 A Name1
40 A Name2
50 A Name3

What awould be the suitable approach
 
Jeanne Boyarsky
author & internet detective
Marshal
Posts: 34084
337
Eclipse IDE Java VI Editor
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Viswa,
You don't need a nested query for this.



The above SQL displays the maximum value for each name. I left out col2 since it is redundant - you already know it is A. If you want it returned for formatting, you can add it to the select clause and group by.
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic