TOP operator - 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

SQL Server 2000 Forums

SQL Server Development (2000)

TOP operator

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2002-01-01 : 23:30:42

amit writes "Hello,

I am using the TOP operator to select the first five rows from a table as below:

SELECT TOP 5 ClientNumber
FROM Client

i get the following error:
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near '5'.

is there some server option that needs to be enabled before using the TOP operator?

Thanks in advance,
Amit"

Nazim
A custom title

1408 Posts

Posted - 2002-01-01 : 23:57:16

Sql Server 6.5 doesnt supports Top Operator, incase you are using it

set rowcount 5
select client from client
order by Client
set rowcount 0

HTH

ambekami
Starting Member

3 Posts

Posted - 2002-01-02 : 11:24:30

I am using SQL Server 7.0

smccreadie
Aged Yak Warrior

505 Posts

Posted - 2002-01-02 : 11:28:05

YOu need to specify columns or Select top 5 * from tblname.

Nazim
A custom title

1408 Posts

Posted - 2002-01-02 : 11:32:09

isnt ClientNumber a column in client table.
if so then the problem is something else.

----------------------------
Anything that Doesn't Kills you Makes you Stronger

ambekami
Starting Member

3 Posts

Posted - 2002-01-02 : 11:32:42

quote:

YOu need to specify columns or Select top 5 * from tblname.

I have included a column name (ClientNumber is a colum in the client table). Besides, SELECT TOP 5 * FROM Client gives the same error.

Nazim
A custom title

1408 Posts

Posted - 2002-01-02 : 11:43:52

The error it is generating is for Delimited Identifiers. therez nothing wrong with the top there.

i just tried creating the table with same name and same column and it works fine.

Can you write the query on the new query window.

----------------------------
Anything that Doesn't Kills you Makes you Stronger

ambekami
Starting Member

3 Posts

Posted - 2002-01-02 : 11:46:30

I get the same error again, even if i rewrite the query in a new window.
is there some server option that as te be enabled/disabled before using TOP?

Nazim
A custom title

1408 Posts

Posted - 2002-01-03 : 01:17:50

Am about to give it up. i dont know of any such options with Sql Server . if therez any someone will enlighten us.

Can u try something for me again.

use Top Operator with a new table with inserting some data in it. and can you check whether it is working or not.

----------------------------
Anything that Doesn't Kills you Makes you Stronger

Edited by - Nazim on 01/03/2002 01:18:55

Merkin
Funky Drop Bear Fearing SQL Dude!

4970 Posts

Posted - 2002-01-03 : 03:00:40

Hi

Is that the exact query you are using ? Or are just posting a small part of it, or a subquery etc ?

There shouldn't be anything wrong with what you are posting here so I suspect there is more in your actual query.

Damian

graz
Chief SQLTeam Crack Dealer

4149 Posts

Posted - 2002-01-03 : 09:57:33

I'd be willing to bet you dollars to donoughts that either (a) you really aren't using SQL 7.0 or (b) your database compatability is set to version 6.5.

Can you run

sp_dbcmptlevel @dbname='pubs'

except replace pubs with your database. If you get 70 that's not the problem. I'm guessing you'll get 65. In either case can you also post the exact SQL and error message? I'm thinking you already did but just in case?

===============================================
Creating tomorrow's legacy systems today.
One crisis at a time.

skweeky
Starting Member

9 Posts

Posted - 2002-01-03 : 10:13:26

hi all !
try to apply SP1 if you havent it, i think it would better works !!!

Da_Retina
Posting Yak Master

109 Posts

Posted - 2002-01-06 : 03:16:40

Salute...
I also bet on the compatability level .. I had this problem and fixed it by changing the cmptlevl to 70!

Rafiq
Starting Member

25 Posts

Posted - 2002-01-07 : 00:45:35

quote:

I am using the TOP operator to select the first five rows from a table as below:

SELECT TOP 5 ClientNumber
FROM Client

i get the following error:
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near '5'.

is there some server option that needs to be enabled before using the TOP operator?

Hi,

The Microsoft says like this,

The compatibility setting also has an effect on reserved keywords. This table shows the words reserved at the specified level, but valid for use in object names at lower levels. At lower compatibility levels, the language features corresponding to the reserved keywords in upper levels are not available.

Compatibility Reserved keywords
level
setting

80 COLLATE, FUNCTION, OPENXML

70 BACKUP, CONTAINS, CONTAINSTABLE, DENY, FREETEXT,
FREETEXTTABLE, PERCENT, RESTORE, ROWGUIDCOL, TOP

65 AUTHORIZATION, CASCADE, CROSS, DISTRIBUTED, ESCAPE,
FULL, INNER, JOIN, LEFT, OUTER, PRIVILEGES,
RESTRICT, RIGHT, SCHEMA, WORK

The compatibility level setting cannot be changed in the master database, but it can be changed in the model database to take effect in all new databases. The compatibility level cannot be changed inside a stored procedure or in Transact-SQL strings executed with the EXEC('string') syntax. The compatibility level should not be changed inside a batch of Transact-SQL statements.

First of all, check your existing DB compatibility level. If its 65 then, change your DB compatibility level as 70.

EXEC sp_dbcmptlevel 'your DB name', 70

Regards,

Rafiq
------------------------------------
If you think, you can do anything...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources