SQL Query?

[nowayout]

Hi all,

How would i get the top 10 record from the table i tried the following but it's not working

select top 10 * from job_info

 

[sunila7]

Hi,

Is there a specific order in which u want the records if so then

Try

select top 10 * from job_info order by fld1 desc

Hope it helps
Sunil

 

[golandsh]

It works just fine

 

[golandsh]

you propably didnt write the table name properly

 

[nowayout]

it gives me error as followed

Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near '10'.

thanks,,

 

[sunila7]

Hi,

r u writing a view? if so order by cannot be used unless u use a

Select Top 10 * From job_info order by fld1 desc
Or
u can use
Select Top 100 Percent * From job_info order by fld1 desc

But it is not a good idea to put order by in a view...

u can do a select from view later on...
select * from vwjob_info Order by fld1 desc

Sunil

 

[nowayout]

no i am just querying on the data but it still gives me error that incorrect systax near '10' so what can it be?


Thanks,,

 

[sunila7]

Hi,

Can u paste ur SQL which u r executing... looks to be a problem with that

Select Top 10 * From job_info order by fld1 desc

is the synatax....

Sunil

 

[nowayout]

select top 10 * from job_info

that;s all my developer trying to do and i tried to but not working somehow

 

[JamesLean]

What version of SQL Server are you using? Must be 7+ to use TOP.

If this is the case, check the compatibility level of the DB. I had this problem when I upgraded to SQL 7 and the compat level was still on 65. --James

 

[sunila7]

Hi,

Try this

Set Rowcount 10

select * from job_info

Set Rowcount 0

Does this work

Sunil

 

[nowayout]

i have SQL 2k

how would i check compatibility?

 

[sunila7]

Hi,

In Enterprise manager select the Database and right click and get to properties and select options tab... u will find a combo box for compatiblity...


Sunil

 

[JamesLean]

Use the system stored proc sp_dbcmptlevel to retrieve the current level like this:

EXEC sp_dbcmptlevel 'db_name'

To change it to 2000 use:

EXEC sp_dbcmptlevel 'db_name', 80

--James

 

[nowayout]

got it solved the compatibility problem was the main concern



thanks to all of you