SQL Select Top Problem

[begbie]

I have one database on an SQL server where i can execute a simple Select Top Command eg( Select Top 5 Name From Client List order by Sales), but on another database (same server) the exact same syntax applied to a different table will not work. Is there some database level option that can be set which would enable/disable the "Select Top" syntax?

 

[tlbroadbent]

Which RDMS are you using. In SQL Server, TOP only works with SQL 7 compatible databases are higher. If you are using SQL Server, check the compatibility level of the database that doesn't work with TOP.

sp_dbcmptlevel 'dbname' will check the compatibility level. sp_dbcmptlevel 'dbname' 70 will set the level to 7. Changing the compatibility level will alter other behviors in sql server so be cautious. You can change the compatibility level for the query and then change it back after the query. For example, ...

sp_dbcmptlevel 'dbname' 70
Go

Select top ....
Go

sp_dbcmptlevel 'dbname' 65
Go Terry Broadbent
Please review faq183-874.

"The greatest obstacle to discovery is not ignorance -- it is the illusion of knowledge." - Daniel J Boorstin

 

[begbie]

I managed to get there on my own, but yes you were right. The database was set at compatibility level 65. Thanks for the reply.