MS access Query help - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Other Forums

MS Access

MS access Query help

Author

Topic

keny
Starting Member

1 Post

Posted - 2003-09-08 : 15:50:24

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

Stoad
Freaky Yak Linguist

1983 Posts

Posted - 2003-09-08 : 17:59:44

Just because exactly in this manner Jet SQL TOP N predicate works.

In your first query there is only one row with min(startdate) value.
I mean min value of startdate within the entire table. Obviously, it
is a pure accident.

But when you apply the WHERE clause filter then among these
filtered rows there are exactly 3 rows with min(startdate) value,
of course in this case this min value is up to the filtered rowset.

Try to add into ORDER BY clause of the 2nd query other fields and
for sure you'll see a difference.

X002548
Not Just a Number

15586 Posts

Posted - 2003-09-09 : 14:31:45

I can't get access to do that...can you post some DML and DDL?

Stoad TOP 1 is TOP 1, no?

Brett

8-)

SELECT @@POST=NewId()

That's correct! It's an AlphaNumeric!

Stoad
Freaky Yak Linguist

1983 Posts

Posted - 2003-09-09 : 15:02:15

Sample for you:


--------------
m1    m2    n
--------------
1     2     1
1     2     2
2     2     3
2     2     4
33    3     5
44    4     6
--------

select top 1 * from t order by m2 will return 4 'first' rows.

But select top 1 * from t order by m2, m1 will return 2 'first' rows.

select top 1 * from t order by m2, m1, n will return 1 'first' row.

So it works.

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-09-09 : 15:07:36

WOW! i never knew that.

Brett --

create a table like this:

EmpID,Salary
1,100
2,200
3,300
4,300
5,300

now do:

select top 1 empID, salary from tbl order by salary Desc

it returns all 3 guys with a salary of $300 !

i must admit, that really surprises me. I guess Stoad is right, it's an implementation decision made by MS about Access and that's how it chooses to implement the "TOP" predicate.

- Jeff

Stoad
Freaky Yak Linguist

1983 Posts

Posted - 2003-09-09 : 15:26:33

Hehe...
Jeff plz don't ask me how long I know this amazing fact. LOL.

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-09-09 : 15:28:40

Stoad --

I am humbled by your Access knowledge !!!

- Jeff

Stoad
Freaky Yak Linguist

1983 Posts

Posted - 2003-09-09 : 15:39:06

Oh, Jeff...

Seems you have not got my self-irony. LOL.

X002548
Not Just a Number

15586 Posts

Posted - 2003-09-09 : 15:41:24

That what I like about MS...

Standardization...and portability...

How long has TOP been in Access anyway?

Don't think it was around in 95...it's there in 97 though..

Brett

8-)

SELECT @@POST=NewId()

That's correct! It's an AlphaNumeric!

robvolk
Most Valuable Yak

15732 Posts

Posted - 2003-09-09 : 21:56:47

It was in 95, and I think it might have been in 2.0 too, but I honestly can't remember that far back.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources