Re: Top N Records on multiple fields
- From: "Michel Walsh" <vanderghast@VirusAreFunnierThanSpam>
- Date: Tue, 17 Jun 2008 10:00:52 -0400
You can rank by group, or use a sub-query.
It is easier if you have a primary key: pk
Ranking by group:
-------------------------------------------
SELECT a.concatenation, a.pk,
LAST(a.month), LAST(a.year), LAST(a.vendor), LAST(a.mmc), LAST(a.sales)
FROM table AS a INNER JOIN table AS b
ON a.concatenation = b.concatenation
AND a.sales >= b.sales
AND a.pk >= b.pk
GROUP BY a.concatenation, a.pk
HAVING COUNT(*) <= 10
ORDER BY a.concatenation, LAST(a.sales) DESC
--------------------------------------------
Using sub-query:
------------------------------------------
SELECT concatenation, pk, month, year, vendor, mmc, sales
FROM table AS a
WHERE pk IN( SELECT TOP 10 b.pk
FROM table AS b
WHERE b.concatenation = a.concatenation
ORDER BY b.sales DESC, b,pk)
ORDER BY concatenation, sales DESC
-------------------------------------------
(probably slower, though)
The last ORDER BY clause, ofeach of these queries, is just to get a nice
output, but that is not necessary to just 'get' the records.
Vanderghast, Access MVP
"CodeCrazy" <CodeCrazy@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:4FF50C6B-FEAA-4A20-94B5-76093274CB8D@xxxxxxxxxxxxxxxx
Hello -
I have a table in access that I need to pull the Top 20 records for each
different concatenation field. Here is an example table:
Concatenation Month Year Vendor MMC Sales
16-1000 5 2008 12345 1000 20000
16-1000 5 2008 12345 1000 15000
16-1000 5 2008 12345 1000 10000
16-2000 5 2008 56789 2000 20000
16-2000 5 2008 56789 2000 15000
16-2000 5 2008 56789 2000 10000
16-3000 5 2008 45678 3000 20000
16-3000 5 2008 56789 3000 15000
16-3000 5 2008 56789 3000 10000
Each concatenation has hundreds of rows. I need the Top 20 for each
different concatenation. Is that possible? If I select Top 20 values it
only returns the first 20 for concatenation 16-1000. Any and all help
greatly appreciated!
Thanks
.
- Follow-Ups:
- Re: Top N Records on multiple fields
- From: Anthony
- Re: Top N Records on multiple fields
- From: CodeCrazy
- Re: Top N Records on multiple fields
- Prev by Date: Re: TOP in query user selects amount to return
- Next by Date: Re: will not accept product key supplied with computer
- Previous by thread: EXTRACT DATA FROM FIELD
- Next by thread: Re: Top N Records on multiple fields
- Index(es):
Relevant Pages
|
