Query gives different results

[sumdumgai]

Hello, can someone please explain why the following SQL query might give different results if executed within Access and executed within SQL Server?

SELECT top 200 * from [table name]

Thanks.

 

[jdraw]

What do you think it does?

You need to identify a column Name instead of *.

 

[sumdumgai]

Sorry. I wasn't clear. I have an SQL database with table with field named 'ID', primary key, clustered. When the table is loaded, the records are loaded in physical order: ID = 1,2,3, etc. Hence, when I do the query mentioned above within the SQL database, I get records with ID = 1,2,3. etc in that order. However, when that table is linked to Access and I run the same query in Access, I get records with ID = 1,5,23,etc. Why doesn't Access display results same as SQL?

 

[RogerCooper]

A "top" value query must have a sort or the results will be unpredictable even with a keyed table.

 

[arnelgp]

will ordering solved your problem:

select top 200 from table order by [id];

 

[jdraw]

You have to identify the field from which to do the select.

sample

SELECT TOP 2 
FROM AnimalLocs
ORDER BY AnimalLocs.gpslat ;

results in an error as attached

SELECT TOP 2 gpslat
FROM AnimalLocs
ORDER BY AnimalLocs.gpslat ;

results in

gpslat
45.32
46.8 since Ascending is the default Order in Access

and results in
gpslat
49.4
49.3 when you add DESC to the Order BY.

 

[sumdumgai]

Thank you.