Top 50 Query

[E.Ehrhard]

Can anyone help me acomplish the following.
I have a list of data like this.

Group | ProductGr | FYPeriod | Material | Quantity
---------------------------------------------------
Int Software Mar03 1245555 95
Ext Equipment Jan04 5554644 6
Ven Equipment Mar03 1255799 1555

Etc... I am trying to extrapolate the top 50 records by
quantity for each Fiscal Period for each product group
and for each group. Short of writing a query for each and
using TopValues is there any way to acomplish what i am
attempting to do?

Thank you in advance.

Ezra

 

[Brian Camire]

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

SELECT
[Your Table].*
FROM
[Your Table]
WHERE
[Your Table].[Material]
IN
(SELECT TOP 50
[Self].[Material]
FROM
[Your Table] AS [Self]
WHERE
[Self].[Group] = [Your Table].[Group]
AND
[Self].[ProductGr] = [Your Table].[ProductGr]
AND
[Self].[FYPeriod] = [Your Table].[FYPeriod]
ORDER BY
[Self].[Quantity] DESC)