select top N records

Question

Post reply

select top N records

sandy-833685

SSC Eights!

Points: 919
More actions
April 7, 2009 at 1:22 am

#129775

Hi,
i am working on sql 2000
when i run below query
declare @n int
set @n=10
select top @n * from emp_detail
where id not in(select top @n id from emp_detail order by id desc)
order by id desc
it gives me error
Server: Msg 170, Level 15, State 1, Line 3
Line 3: Incorrect syntax near '@n'.
Server: Msg 170, Level 15, State 1, Line 4
Line 4: Incorrect syntax near '@n'.
and same if i do for delete it gives same error
can some one please help me in this

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 1

Hi Sandy

You're missing the parentheses around the variable:

select top (@n) * from emp_detail

Cheers

ChrisM

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

tcnolan SSCrazy Points: 2097 More actions · Answer 2

Actually SQL 2000 does not allow you to paramaterize the TOP clause of a SELECT statement. You need to be running at least SQL 2005 for this, in which case your syntax would work fine.

Also, the parentheses are not required for SELECT statements, but is not recomended by Microsoft as a best practice according to Books Online for SQL 2005/2008. Parentheses are required for INSERT, UPDATE, and DELETE.

http://msdn.microsoft.com/en-us/library/ms189463(SQL.90).aspx - SQL 2005

http://msdn.microsoft.com/en-us/library/ms189463.aspx - SQL 2008

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 3

tnolan (4/7/2009)
Actually SQL 2000 does not allow you to paramaterize the TOP clause of a SELECT statement. You need to be running at least SQL 2005 for this, in which case your syntax would work fine.

Yes that's correct - and this is a SQL2k5 forum section

tnolan (4/7/2009)
Also, the parentheses are not required for SELECT statements, but is not recomended by Microsoft as a best practice according to Books Online for SQL 2005/2008. Parentheses are required for INSERT, UPDATE, and DELETE.

The parens are required for SELECT in SQL2k8.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

tcnolan SSCrazy Points: 2097 More actions · Answer 4

Agreed, wrong section.

For SQL2008 support for parenthesis though...

SQL 2008 BOL for TOP - http://msdn.microsoft.com/en-us/library/ms189463.aspx
Parentheses that delimit expression in TOP is required in INSERT, UPDATE, MERGE, and DELETE statements. For backward compatibility, TOP expression without parentheses in SELECT statements is supported, but we do not recommend this.

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 5

tnolan (4/7/2009)
Agreed, wrong section.
For SQL2008 support for parenthesis though...
SQL 2008 BOL for TOP - http://msdn.microsoft.com/en-us/library/ms189463.aspx
Parentheses that delimit expression in TOP is required in INSERT, UPDATE, MERGE, and DELETE statements. For backward compatibility, TOP expression without parentheses in SELECT statements is supported, but we do not recommend this.

That's amusing... here's my version:

Microsoft SQL Server Management Studio Complete (expires in 70 days)10.0.1600.22 ((SQL_PreRelease).080709-1414 )
Microsoft Analysis Services Client Tools2007.0100.1600.022 ((SQL_PreRelease).080709-1414 )
Microsoft Data Access Components (MDAC)6.0.6001.18000 (longhorn_rtm.080118-1840)
Microsoft MSXML3.0 4.0 5.0 6.0
Microsoft Internet Explorer7.0.6001.18000
Microsoft .NET Framework2.0.50727.3074
Operating System6.0.6001

Here's the code:

DROP table #testMAX

create table #testMAX (col1 int, col2 int, col3 int)

insert into #testMAX (col1, col2, col3)

SELECT 5, 1, 1 UNION ALL

SELECT 4, 2, 2 UNION ALL

SELECT 3, 3, 5 UNION ALL

SELECT 2, 4, 4 UNION ALL

SELECT 1, 5, 3

declare @top int

set @top = 2

select top (@top) * from #testMAX order by col1

-- returns 2 rows

select top @top * from #testMAX order by col1

-- Msg 102, Level 15, State 1, Line 1 Incorrect syntax near '@top'.

But Sandy says above

Hi,
i am working on sql 2000

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

sandy-833685 SSC Eights! Points: 919 More actions · Answer 6

yeah i agree that this is sql2k5 forum

but thought can get some clue for sql2k but didnt fine even after puttin parenthises

tcnolan SSCrazy Points: 2097 More actions · Answer 7

That is pretty amusing. Apparently the QC process for BOL is not entirely thorough. 😉 Thanks for clearing that up though, good to know even BOL can't always be trusted.

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 8

sandy (4/7/2009)
yeah i agree that this is sql2k5 forum
but thought can get some clue for sql2k but didnt fine even after puttin parenthises

Hi Sandy

If you are working with SQL2k then you could do this with dynamic SQL - but perhaps alternatives should be sought first? Can you provide a more complete description of the task you are working on?

@tnolan: No worries mate:cool:

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden