Problem using TOP in select

[dummieq]

Hi,
I am trying to use TOP in Select query to restrict the number of rows. I always get an error saying

Incorrect syntax near '20'.

for the sql query
Select TOP 20 from companies;

Can someone tell me how to use Top and what the prb might be.

Thanks
dummie

 

[sunila7]

Hi,

Try this...

Select TOP 20 * from companies




Sunil

 

[dummieq]

I tried that too..with the same result

Any other suggestions?

Thanks

 

[sunila7]

Hi,

Few Questions..
1. what is the Database u r using?
2. Is that the only statement u r trying to execute, or is there a buch of T-SQL stmnt u r trying to execute?
If the database is SQL server then TOP command might not work, and if it is SQL SERVER then syntax I gave is correct, so if u r having a bunch of statements being excuted then the error might be on previuos lines.

Hope it helps



Sunil

 

[SQLBill]

Are you using Microsoft SQL Server?

Looking at your query, I noticed you have a semicolon ( at the end. This isn't proper MS SQL Server syntax, so I'm guessing you are using ORACLE or some other SQL platform. If so, you need to post in the proper forum.

If you are using MS SQL Server, Sunil's suggestion should have worked.

The problem with your query is that you didn't tell Microsoft SQL Server WHAT you wanted the TOP 20 of.

-SQLBill

 

[dummieq]

hi,
Yes, db is SQL server. Also I am trying to execute only that stmt. Why do u say that it might not work if it is Sql server. The version is 2000.

Thanks again
dummie

 

[SQLBill]

Why do u say that it might not work if it is Sql server. The version is 2000.

I didn't say it might not work. It's just that the semicolon isn't used in SQL Server to end a statement. GO is used...

SELECT TOP 20 *
FROM companies
GO

When you run the above, what errors do you get? Are you running the command in the correct database?

One way to make sure you are using the correct database is to add the USE command (for example, if you are using NorthWind db):

USE northwind
GO
SELECT TOP 20 *
FROM companies
GO

-SQLBill

 

[sunila7]

Hi,

I am sorry wht I meant was

"If the database is not SQL server then TOP command might not work"




Sunil

 

[dummieq]

Hi,
I followed the suggestion of specifying the db explicitly and it worked. Thanks for that. But I still don't understand why it works for "Select * .." and fails for "Select Top 5 *.." when db is not specied explicitly.



Thanks
dummie

 

[SQLBill]

Are you sure you were running the query in the correct database? I run TOP 10 queries quite a bit and they work whether or not I use the USE database command.

But I have at times forgotten to make sure the correct database is being used. That's why I now include the USE command in all my scripts.

-SQLBill