Top 3 for each product

[Guest]

Hello,
I need to be able to select the top 3 rankings for each product group.

I have a ranked table with prodgrpID, prodName, ranking (poor, good,
excellent..). I also have another table that translates the ranking into a
number (1 - 5). How do I write a query in access 2000 to show me only the
top 3 rankings for each group (ie. if I have more than 3 with the same top
ranking, it picks the first 3 in alphabetical order)?

eg. prodgrpID prodName ranking
A www 5
A xxx 5
A yyy 3
A zzz 3
B www 5
B xxx 3
B yyy 3
B zzz 2

Results should be:
prodgrpID prodName ranking
A www 5
A xxx 5
A yyy 3
B www 5
B xxx 3
B yyy 3

Thanks.

 

[Brian Camire]

You might try a query whose SQL looks something like this:

SELECT
[prodgrpID],
[prodName],
[ranking]
FROM
[Your Table]
WHERE
[prodName]
IN
(SELECT TOP 3
[Self].[prodName]
FROM
[Your Table] AS [Self]
WHERE
[Self].[prodgrpID] = [Your Table].[prodgrpID]
ORDER BY
[Self].[ranking] DESC,
[Self].[prodName])

This assumes the combination of [prodgrpID] and [prodName] is unique.

 

[Guest]

Thanks!!!

You might try a query whose SQL looks something like this:

SELECT
[prodgrpID],
[prodName],
[ranking]
FROM
[Your Table]
WHERE
[prodName]
IN
(SELECT TOP 3
[Self].[prodName]
FROM
[Your Table] AS [Self]
WHERE
[Self].[prodgrpID] = [Your Table].[prodgrpID]
ORDER BY
[Self].[ranking] DESC,
[Self].[prodName])

This assumes the combination of [prodgrpID] and [prodName] is unique.

Hello,
I need to be able to select the top 3 rankings for each product group.

I have a ranked table with prodgrpID, prodName, ranking (poor, good,
excellent..). I also have another table that translates the ranking into a
number (1 - 5). How do I write a query in access 2000 to show me only the
top 3 rankings for each group (ie. if I have more than 3 with the same top
ranking, it picks the first 3 in alphabetical order)?

eg. prodgrpID prodName ranking
A www 5
A xxx 5
A yyy 3
A zzz 3
B www 5
B xxx 3
B yyy 3
B zzz 2

Results should be:
prodgrpID prodName ranking
A www 5
A xxx 5
A yyy 3
B www 5
B xxx 3
B yyy 3

Thanks.