Top 5

  • Thread starter Thread starter Heather
  • Start date Start date
H

Heather

Hello,

Is their a way to get the top five per each ID. So say
each ID has 21 funds, I want the top 5 sales for Each ID.

ID Fund Amount
1111 A 100
1111 k 99
1111 B 98
1111 C 97
1111 D 96
2222 E 101
2222 A 78
2222 B 55
2222 C 22
2222 D 10
 
You might try a query whose SQL looks something like this:

SELECT
[Your Table].*
FROM
[Your Table]
WHERE
[Your Table].[Fund]
IN
(SELECT TOP 5
[Self].[Fund]
FROM
[Your Table] AS [Self]
WHERE
[Self].[ID] = [Your Table].[ID]
ORDER BY
[Self].[Amount] DESC)

You may get more than 5 records for each ID if there are ties.
 
I'm not an expert, but I had a similar situation. In
design view of the query you are creating there is a Top
Value box in your toolbar. Enter the number 5 in it and
it will give you the top five.
 
Brian Thanks!!

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

SELECT
[Your Table].*
FROM
[Your Table]
WHERE
[Your Table].[Fund]
IN
(SELECT TOP 5
[Self].[Fund]
FROM
[Your Table] AS [Self]
WHERE
[Self].[ID] = [Your Table].[ID]
ORDER BY
[Self].[Amount] DESC)

You may get more than 5 records for each ID if there are ties.

Hello,

Is their a way to get the top five per each ID. So say
each ID has 21 funds, I want the top 5 sales for Each ID.

ID Fund Amount
1111 A 100
1111 k 99
1111 B 98
1111 C 97
1111 D 96
2222 E 101
2222 A 78
2222 B 55
2222 C 22
2222 D 10
Click to expand...


.
Click to expand...
 
Back
Top