--SELECT * 
DELETE t
FROM 
(SELECT ROW_NUMBER() OVER (ORDER BY [index #] ASC) AS Seq
--,*
FROM table
)t
WHERE Seq <=300

create table #rate (rate int)
declare @f int = 0
while @f < 400
begin
insert into #rate 
select @f 

set @f +=1 
end

select * from #rate


SELECT *
FROM
(SELECT ROW_NUMBER() OVER (ORDER BY #rate.rate ASC) AS Seq
FROM #rate ) t
WHERE Seq <=300

quote:

---

Originally posted by narf

i can't tell which statement errors because the error message isn't any more specific than i was :)

SELECT *
FROM
(SELECT ROW_NUMBER() OVER (ORDER BY rate.rate ASC) AS Seq
FROM rate ) t
WHERE Seq <=300

the index column name and the table name both are named "rate"

---

SELECT @@VERSION
GO
EXEC sp_dbcmptlevel 'yourdbname'
GO

quote:

---

Originally posted by narf

thank you very much for your answers...

@DonAtWork: your statements don't work for me... "syntax error near #rate", but i think, it's the same problem as visakh16 is trying to find out :)

@ visakh16: that statement doesn't work either ("unrecognized token @") :) i'm using SQLite Expert with an sqlite database. i'm not sure how compatible that is to sql 2005... but i think the compatibility is limited ;)

---

quote:

---

If SQLite is compiled with the SQLITE_ENABLE_UPDATE_DELETE_LIMIT compile-time option, then the syntax of the DELETE statement is extended by the addition of optional ORDER BY and LIMIT clauses:

---

quote:

---

Originally posted by narf

quote:

---

If SQLite is compiled with the SQLITE_ENABLE_UPDATE_DELETE_LIMIT compile-time option, then the syntax of the DELETE statement is extended by the addition of optional ORDER BY and LIMIT clauses:

---

that LOOKED so good. but unfortunately, neither sqlite expert nor an sqlite shell recognized the "limit" param :/

but thanks anyway for your answer :)

---

quote:

---

Originally posted by kumaraswamy

Hi narf,
i am send the code used in server 2008 with a small example.
it might help u

create rate
(
id int,
name nvarchar(10)
)

insert into rate values(1,'a'),(4,'b'),(6,'r'),(2,'w'),(12,'s')

--using common table expression

with temp
as
(select ROW_NUMBER() over(order by id asc) as num,* from rate )
delete from temp where id<=5

kumaraswamy reddy

---