MS access Query help

K

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
 
R

Rick Brandt

Rick Brandt said:
The TOP predicate will include more records than specified if there are "ties". Do
the three records all have the same AssettId value?
Click to expand...

The above should have read...

Do the three records all have the same startdate?
 
R

Rick Brandt

keny said:
Yes the 3 records have same startdate .
is there any way out?
Click to expand...

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

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
.
Click to expand...

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
 
Top