AccessBlog.net

News, links, downloads, tips and tricks on Microsoft Access and related

About Me Search
Alex
Name:Alex Dybenko

Location:Moscow, Russia

Tuesday, September 13, 2005

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:

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

2 Comments:

Anonymous Anonymous said...

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

4:47 PM  
Blogger Alex Dybenko said...

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"

11:04 AM  

Post a Comment

<< Home