From this table File1
File1 AOU Data Type Number1 Number Number Number
I have created a simple Select query in order to determine the top 3 rows (number1) within AOU.
SELECT File1.AOU, File1.NumberCount, File1.number1
FROM File1
WHERE (((File1.AOU)=130) AND ((File1.number1) In (SELECT TOP 3 number1
FROM File1 as temp
WHERE temp.AOU = File1.AOU)));
From this I get the following results by selecting just one AOU number (as an example):
AOU NumberCount number1 130 1 1087 130 1 447 130 1 511
The results looks good except that the numbers in the 3rd column are incorrect as they are not the top 3 values.
See entire table below.
Also if I run a query with a sort field entry added:
SELECT File1.AOU, File1.NumberCount, File1.number1
FROM File1
WHERE (((File1.AOU)=130) AND ((File1.number1) In (SELECT TOP 3 number1
FROM File1 as temp
WHERE temp.AOU = File1.AOU
ORDER BY AOU)));
AOU Number Count number1 130 2 1940 130 1 1722 130 1 1529 130 1 1449 130 1 1280 130 1 1206 130 1 1087 130 1 1061 130 1 895 130 1 890 130 1 795 130 1 717 130 1 670 130 1 623 130 1 569 130 1 517 130 1 511 130 1 447 130 1 434 130 1 411 130 1 402 130 1 351 130 1 272 130 1 250 130 1 231 130 1 212 130 1 194 130 1 102 130 1 93 130 1 40 130 1 17 130 1 12 130 1 6 130 1 5 130 2 2 130 1 1
The result is the return of all records that meet the criteria "130"
2 Questions:
What is incorrect with the 1st query that runs OK by does not return the correct top 3 values?
Why does adding a sort field ignore the "IN (Select Top 3" subquery return all relevant fields regardless of their value?
Thank you for any help or suggestions!
Roger Troutman