Delete the first 3 rows of a table

[Dom71555]

I am trying to delete the first 3 records in a table but am at a loss as how to do it.

Table looks like this...
create table #classCuts (
lastname varchar(50),
firstname varchar(50),
studentnumber varchar(15),
coursenumber varchar(13),
sectionnumber int,
coursename varchar(30),
periodname varchar(10),
teacherdisplay varchar(30),
cutdate smalldatetime

If a student cuts a class more than 3 times, I want to remove the first 3 records, yet keep the other records so teachers can write a referral. Data would look like this in the table..
xxx Joseph 6519 103 10 ENGLISH 10 8 Mr. Teacher 10/28/2009
xxx Joseph 6519 103 10 ENGLISH 10 8 Mr. Teacher 11/4/2009
xxx Joseph 6519 103 10 ENGLISH 10 8 Mr. Teacher 11/13/2009
xxx Joseph 6519 103 10 ENGLISH 10 8 Mr. Teacher 11/17/2009

The end result will leave only 1 record in the table. The first 3 deleted.
I tried a cursor to do this but am stuck with the syntax.

 

[bborissov]

How do you know which are the "first" 3 records?
And what version of SQL Server you use?

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[markros]

With SQL Server 2005 and up:

;with cte as (select *, row_number() over (partition by StudentNumber order by CutDate) as row from #classCuts)

delete from cte where Row <=3

PluralSight Learning Library

 

[r937]

markros, i don't think that's quite right

the original spec is "If a student cuts a class more than 3 times..."

your query will delete all rows for any student that has cut a class once, twice, or three times, but not more than three times

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[markros]

Good point, I didn't notice it as a requirement, but it's very easy to fix:

;with cte as (select *, count(*) pver (partition by StudentNumber) as cntCuts, row_number() over (partition by StudentNumber order by CutDate) as row from #classCuts)

delete from cte where Row <=3 and cntCuts > 3

PluralSight Learning Library

 

[r937]

can you really use the dreaded, evil "select star" along with COUNT(*)? without a GROUP BY?



r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[markros]

Yes, sure - BTW, just noticed I made a typo in the word OVER.

PluralSight Learning Library

 

[Dom71555]

Thank you all for your advice and I should have told you I am using SQL Server 2000. Unfortunatly we did not upgrade to 2005 yet. I'm guessing your code will not work with sql server 2000 Markros, is that true?

 

[Dom71555]

bborissov, the first three cuts would be the earliest cut dates.

 

[markros]

It would not work in SQL Server 2000. The problem becomes a bit more complex then.

In this table, do you have a unique ID? We need an ID here. Create a table with the same structure and extra ID field (identity (1,1) primary key). After that the query will be like this

select T.* from myTable T inner join (select StudentNumber from myTable group by StudentNumber having count(*) >=3) X
on T.StudentNumber = X.StudentNumbewr
where ID in (select top 3 ID from myTable T1 where T.StudentNumber = T1.StudentNumber order by CutDate)

PluralSight Learning Library

 

[Dom71555]

Thanks Markros, the sql works, however, it returns the first 3 cuts a student has. I need to have the first 3 cuts removed and the remainder of the cuts(records) displayed.

I guess I can insert those records returned by your sql in a table, then delete from the main table where they are equal.

I did create a new table with an identity as the primary key.

 

[markros]

I showed you the SELECT statement so you can verify that it does do what you need. Now, when you verified it, change select to delete, e.g.

delete from  #classCuts  T 
inner join (select StudentNumber from #classCuts group by StudentNumber having count(*) >=3) X
on T.StudentNumber = X.StudentNumbewr
where ID in (select top 3 ID from  #classCuts  T1 where T.StudentNumber = T1.StudentNumber order by CutDate) 

select * from  #classCuts

PluralSight Learning Library

 

[Dom71555]

I get some errors.....

delete from wfsd_student_cuts_keyed T inner join (select StudentNumber from wfsd_student_cuts_keyed group by StudentNumber having count(*) >=3) X
on T.StudentNumber = X.StudentNumber
where rec_no in (select top 3 rec_no from wfsd_student_cuts_keyed T1 where T.StudentNumber = T1.StudentNumber order by CutDate)

Error: Line 1: Incorrect syntax near 'T'. (State:37000, Native Code: AA)
Error: Line 2: Incorrect syntax near 'X'. (State:37000, Native Code: AA)
Error: Incorrect syntax near the keyword 'order'. (State:37000, Native Code: 9C)

 

[markros]

Try instead

delete T from myTable T ...

PluralSight Learning Library

 

[Dom71555]

The syntax worked, however, the first 3 cuts never got deleted.

 

[Dom71555]

markros, is there a way i can use the select statement you provided me that goes out and gets the first three cuts and somehow use that in an update statement to set the field "deletefield" = 1, then go and delete any record that contains a 1 in "deletefield"?

I do appreciate all your help

 

[markros]

Sure, instead of DELETE T From change it to

UPDATE T set DeleteField = 1 from ...

PluralSight Learning Library

 

[Dom71555]

nope, didn't work. It didn't set the first 3 cuts to "1

 

[markros]

It means the select statement didn't produce the right result either. I don't see how the select could have worked, but DELETE or UPDATE didn't. After all, it's exactly the same criteria.



PluralSight Learning Library

 

[Dom71555]

ok,
thanks for all your help. I appreciate it. You gave me enough to go on.

Thanks!