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
at

2 comments:

  1. Anonymous4:47 PM

    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

    ReplyDelete
  2. Alex Dybenko11:04 AM

    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"

    ReplyDelete