SQL to delete the first 300 rows? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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

General SQL Server Forums

New to SQL Server Programming

SQL to delete the first 300 rows?

Author

Topic

narf
Starting Member

7 Posts

Posted - 2012-06-10 : 04:53:05

hi folks,

i'm trying to delete the first 300 rows of a table. though the rows have an index number, it has a lot of gaps. so, row 1 has indeed index no. 1, but row 10 could already have index no. 50.

so, is there a possibility to delete the first 300 rows that come up when it's sorted by the index no?

table
----
index #, content
1, blah
10, blahblah
23, blaaah
45, blahhhh
[...]

thank you very much in advance :)

cheers,
narf

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-06-10 : 14:44:34

yep do like below


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

suggest you to first use SELECT instead of DELETE (uncomment commneted part after commenting DELETE) and then make sure it returns exact records you want
Once you're happy then comment them back to make statement DELETE

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

narf
Starting Member

7 Posts

Posted - 2012-06-11 : 06:47:33

thank you very much for your answer, visakh16 :)

i used your sql-statement but i get a syntax error near "("

i tried it without the "order by" statement (...over [index #] as seq), but then i got a syntax error near [index #]. so i think, the upper error is because of the bracket after "over"...

any ideas?

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2012-06-11 : 06:52:58

1. Post the statement that errors.
2. Post the exact column names. Is there a column named index #?

No, you're never too old to Yak'n'Roll if you're too young to die.

narf
Starting Member

7 Posts

Posted - 2012-06-11 : 07:54:40

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"

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2012-06-11 : 11:35:46

[code]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
[/code]

Works like a champ. Perhaps this isnt your FULL sql statement...

How to ask: http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx
For ultra basic questions, follow these links.
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-06-11 : 15:23:54

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"

Are you sure you're using SQL 2005 and above?

what does below return?


SELECT @@VERSION
GO
EXEC sp_dbcmptlevel 'yourdbname'
GO

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

narf
Starting Member

7 Posts

Posted - 2012-06-11 : 15:51:38

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 ;)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-06-11 : 15:55:55

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 ;)

i dont know about sqllite

are you sure your db is sql server?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

narf
Starting Member

7 Posts

Posted - 2012-06-12 : 03:23:04

the db is an "sqlite" db... but i didn't find any better category in this board. but i didn't know that this would even matter.... i thought, an sql statement is an sql statement.. now i know better ;)

can anyone help me transform one of the upper statements into a working statement for sqlite databases?

Sandips
Starting Member

5 Posts

Posted - 2012-06-12 : 07:00:34

CREATE TABLE #Sample
(
ID INT IDENTITY(1,1),
SAMPLE INT
);
GO

INSERT #Sample
SELECT 55
GO 310

DELETE TOP (300) FROM #Sample
GO

SELECT * FROM #Sample
GO
DROP TABLE #Sample
GO
Please let me know if this helps

narf
Starting Member

7 Posts

Posted - 2012-06-12 : 08:38:13

sorry, that doesn't work either :)

- i get a syntax error, when trying to use # in the table name
- it doesn't recognize "GO 310"
- function TOP is unknown (but would be great if it was implemented)

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2012-06-12 : 08:46:01

http://www.sqlite.org/lang_delete.html

No, you're never too old to Yak'n'Roll if you're too young to die.

narf
Starting Member

7 Posts

Posted - 2012-06-12 : 10:21:08

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 :)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-06-12 : 14:59:16

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 :)

search for sqllite forums and post this in it. that would be best bet for you!

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

kumaraswamy
Starting Member

1 Post

Posted - 2012-06-13 : 08:13:59

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

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2012-06-13 : 08:21:24

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

goin round in circles...
we have had this idea already!

No, you're never too old to Yak'n'Roll if you're too young to die.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources