Using 'TOP n' to get multiple queries

[randi63]

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.

 

[AlexCuse]

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.

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

 

[randi63]

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

 

[AlexCuse]

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

 

[r937]

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