Top Values

  • Thread starter Thread starter Randal
  • Start date Start date
R

Randal

I have a query that is grouped by an ID and sums an amount field (AMT1). I
need to see the top 10 amount values for each ID. Is there a way to do this
in a single query? I need every ID and it's top 10 to show on a report.

Thanks,
 
Randal said:
I have a query that is grouped by an ID and sums an amount field (AMT1). I
need to see the top 10 amount values for each ID. Is there a way to do this
in a single query? I need every ID and it's top 10 to show on a report.

Thanks,
Click to expand...
 
To get the top 10 amounts for each ID, You might try a query whose SQL looks
something like this:

SELECT
[Your Table].[ID],
[Your Table].[AMT1]
WHERE
[Your Table].[AMT1] IN
(SELECT TOP 10
[Self].[AMT1]
FROM
[Your Table] AS [Self]
WHERE
[Self].[ID] = [Your Table].[ID])

Note there may be more or less than 10 records returned for each 10. There
will be less than 10 if there are fewer than 10 records in Your Table for
the ID. There may be more than 10 if the combination of ID and AMT1 is not
unique (that is, if there are "ties").

If you want to see the total amount for the ID in each record, create a new
query that joins the query above with your existing query on the ID field.
 
Brian,

This works great. My only problem is that it takes forever to run if you
have a large table. Do you have any ideas on how I could make it run
faster?
Thanks,

Brian Camire said:
To get the top 10 amounts for each ID, You might try a query whose SQL looks
something like this:

SELECT
[Your Table].[ID],
[Your Table].[AMT1]
WHERE
[Your Table].[AMT1] IN
(SELECT TOP 10
[Self].[AMT1]
FROM
[Your Table] AS [Self]
WHERE
[Self].[ID] = [Your Table].[ID])

Note there may be more or less than 10 records returned for each 10. There
will be less than 10 if there are fewer than 10 records in Your Table for
the ID. There may be more than 10 if the combination of ID and AMT1 is not
unique (that is, if there are "ties").

If you want to see the total amount for the ID in each record, create a new
query that joins the query above with your existing query on the ID field.

Randal said:
I have a query that is grouped by an ID and sums an amount field (AMT1). I
need to see the top 10 amount values for each ID. Is there a way to do this
in a single query? I need every ID and it's top 10 to show on a report.

Thanks,
Click to expand...
Click to expand...
 
I realized that SELECT TOP subquery was missing an ORDER BY clause. Without
this, the results that it returns are arbitrary. The original query was
also missing a FROM clause (which I guess you already figured out). In
other words, the original query should have been:

SELECT
[Your Table].[ID],
[Your Table].[AMT1]
FROM
[Your Table]
WHERE
[Your Table].[AMT1] IN
(SELECT TOP 10
[Self].[AMT1]
FROM
[Your Table] AS [Self]
WHERE
[Self].[ID] = [Your Table].[ID]
ORDER BY
[Self].[AMT1] DESC)

My apologies.

As for making it run faster, you might try to:

1. Create an index on the ID field, if you haven't already.

2. Create an index on the AMT1 field if you haven't already, although I
expect item 1 is likely to help more.

3. Create a composite index on ID (in acending order) and AMT1 (in
descending order), although I expect this may be not much better than item
1.

Hope this helps.


Randal said:
Brian,

This works great. My only problem is that it takes forever to run if you
have a large table. Do you have any ideas on how I could make it run
faster?
Thanks,

Brian Camire said:
To get the top 10 amounts for each ID, You might try a query whose SQL looks
something like this:

SELECT
[Your Table].[ID],
[Your Table].[AMT1]
WHERE
[Your Table].[AMT1] IN
(SELECT TOP 10
[Self].[AMT1]
FROM
[Your Table] AS [Self]
WHERE
[Self].[ID] = [Your Table].[ID])

Note there may be more or less than 10 records returned for each 10. There
will be less than 10 if there are fewer than 10 records in Your Table for
the ID. There may be more than 10 if the combination of ID and AMT1 is not
unique (that is, if there are "ties").

If you want to see the total amount for the ID in each record, create a new
query that joins the query above with your existing query on the ID field.

Randal said:
I have a query that is grouped by an ID and sums an amount field
Click to expand...
(AMT1).
I
need to see the top 10 amount values for each ID. Is there a way to
Click to expand...
do
this
in a single query? I need every ID and it's top 10 to show on a report.

Thanks,
Click to expand...
Click to expand...
Click to expand...
 
Back
Top