Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations gkittelson on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Using 'TOP n' to get multiple queries

Status
Not open for further replies.

randi63

Programmer
Mar 21, 2007
2
I am using Access 2003. I wish to extract the 'TOP n' records for each of about 100 separate items from field item_list without running 100 separate queries. From extensive reading I surmise that I should use a sub-query, but nothing I try seems to work. In each row I want to extract the full record in order then to do further analysis. I would appreciate any help to overcome this problem.
 
Something like this may work for you. Note that the '10' is the number of records you will go for. This assumes that you have some sort of column that determines which records you want (like a timestamp or sequence number), because you need to be able to compare this in your join. Without something to order by, your TOP becomes irrelevant anyway, so I am assuming you have this and calling it SeqCol in my example.

Code:
select a.Item_List
, a.SomeCol
, count(b.SeqCol)
from myTable a
left join myTable b
on a.SeqCol >=b.SeqCol
group by a.Item_List, a.SomeCol
having count(b.SeqCol) <= 10

Hope this helps,

Alex

Ignorance of certain subjects is a great part of wisdom
 
Thanks Alexcuse for your reply. I had problems understanding your proposed solution so I must try and find a different way to describe the problem. Please forgive me for wasting your time.

randi63

The more I learn, the less I understand
 
Can you show sample data? I think that you could run into problems grouping by everything. If thats' the case, you could select/group by only Item_List in the query I posted. You then could join from your main table to this subquery on Item_List = Item_List and SeqCol = SeqCol, to show all columns without the need to group by. Does this make sense?

Hope it helps,

Alex

Ignorance of certain subjects is a great part of wisdom
 
extract the 'TOP n' records for each of about 100 separate items from field item_list without running 100 separate queries"

this is a straightforward query, assuming field "item_list" is not a comma-separated list of id values

:)

r937.com | rudy.ca
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top