• Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Help on SQL Query

 
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: 35276
384
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
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic