Win a copy of Java 9 Revealed this week in the Features new in Java 9 forum!
  • Post Reply Bookmark Topic Watch Topic
  • New Topic

Help on SQL Query  RSS feed

 
Tom Blough
Ranch Hand
Posts: 263
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I need to create a query to find the top 20 vendors and then the top 10 items for each vendor in the first group.
I can create the correlated sub query to find ALL the items for each of the top 20 vendors, but I can not figure out a way to only get the top 10 items for each vendor.
Any ideas?

TIA, Tom Blough
 
Peyton McCullough
Ranch Hand
Posts: 31
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I can't say that I completely get what you are saying, but try 'LIMIT'.
Example:
 
Jeanne Boyarsky
author & internet detective
Marshal
Posts: 36634
475
Eclipse IDE Java VI Editor
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Tom,
To the best of my knowledge that can't all be done in one query. You could use stored procedures to save db round trips though. Or if it's a local database, you could bring back all the products and filter them in java.
 
Tom Blough
Ranch Hand
Posts: 263
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Jeanne,
I ended up having to go with 3 queries. One to select the top 20 vendors, another one to add a ranking of 1-20 to the vendors, and finally a union query that consisted of 20 top 10 queries for the items based on vendor rank.
Thanks for the confirmation that there was no easy way to accomplish this.
Tom
 
It is sorta covered in the JavaRanch Style Guide.
  • Post Reply Bookmark Topic Watch Topic
  • New Topic
Boost this thread!