Top 1 does not return only one record
I was sure that Select top 1 MyField from MyTable Order by OtherField always return 1 record. But it is not! This feature I just recently discovered while helping a guy from Colombia. When this select is ordered - then result depends on values in OtherField. Access help actually states this also: "Returns a certain number of records that fall at the top or the bottom of a range specified by an ORDER BY clause". So if you have a table Journal:
Then following select:
Returns 2 records:
ID Car Miles Date
1 Alfa 100 01/09/2005
2 Alfa 110 02/09/2005
3 Alfa 120 03/09/2005
4 Alfa 130 04/09/2005
5 Alfa 140 04/09/2005
Then following select:
SELECT TOP 1 * FROM Journal Order By Date Desc
Returns 2 records:
ID Car Miles Date
5 Alfa 140 04/09/2005
4 Alfa 130 04/09/2005
posted by Alex Dybenko | 00:38
2 Comments:
Is there any way of using a parameter value for TOP. Or do we always have to put an integer in the SQL code.
I want to vary it using different values, or even a value from a different field.
SELECT TOP x FROM MyTable ORDER BY score
I want x to be a parameter entered by a user.
Graham, Wales
Hi Graham,
you can build query SQL and put user-defined parameter (variable lngTopValues for example) there:
currentdb.querydefs("myquery").SQL="SELECT TOP " & lngTopValues & " FROM MyTable ORDER BY score"
Post a Comment
<< Home