MS access Query help

[keny]

I want to select top 1 record from MS access DB .
If I write a query as ...
select top 1 * from Reservations order by startdate
I get only the top 1 record .If I modify the the query as
select top 1 * from Reservations where AssetId
= '123456' order by startdate
I get 3 records instead on top 1 ... I cannot understand
why .ANY help?

Thanks
Keny

 

[Rick Brandt]

The TOP predicate will include more records than specified if there are "ties". Do
the three records all have the same AssettId value?

The above should have read...

Do the three records all have the same startdate?

 

[keny]

Yes the 3 records have same startdate .
is there any way out?

 

[Rick Brandt]

Yes the 3 records have same startdate .
is there any way out?

You would have to include more fields in the OrderBy clause to break the tie.

 

[keny]

THANKS.....It worked

-----Original Message-----


You would have to include more fields in the OrderBy clause to break the tie.


.

 

[Estuardo]

-----Original Message-----
I want to select top 1 record from MS access DB .
If I write a query as ...
select top 1 * from Reservations order by startdate
I get only the top 1 record .If I modify the the query as
select top 1 * from Reservations where AssetId
= '123456' order by startdate
I get 3 records instead on top 1 ... I cannot understand
why .ANY help?

Thanks
Keny
.

Keny:
The TOP keyword is working just fine, and is doing as its
job.
If you have repeated the stardate the top clause will
look for the Top value, not for even returning the Top
value the N times it appears.
To walkaround this, you must use DISTINCT TOP.

Good Luck

Estuardo