June 17, 2013 at 9:20 pm
Hi
Pls help me to find a Query to delete the records with top 3 marks from a student table
June 17, 2013 at 11:07 pm
Hope this will help,
create table stu_details
(name varchar(1000),
mark int)
insert into stu_details values ('heavenguy',100)
insert into stu_details values ('Tristan',99)
insert into stu_details values ('Jack',98)
insert into stu_details values ('helan',92)
insert into stu_details values ('Jenni',94)
delete a from (
select top 3 * from stu_details order by mark desc) a
June 17, 2013 at 11:15 pm
create table marks
(
student name nvar char(50),
marks int
)
Insert into marks(student name,marks) values('ma ha',67)
Insert into marks(student name,marks) values('tuba',78)
Insert into marks(student name,marks) values('Deena',70)
Insert into marks(student name,marks) values('Oriya',90)
Insert into marks(student name,marks) values('saga',98)
select * from marks
Declare @student name nvar char(50)
Declare @marks-2 int
Declare cc cursor
for
select student name,marks from marks
Group by student name,marks
Having Count(1) > 1
open cc
fetch next from cc into @student name,@marks
While @@FETCH_STATUS = 0
begin
--Declare @student name nvar char(50)
--Declare @marks-2 int
select top 3 @marks-2=marks from marks where @student name=student name order by marks desc
end
fetch next from cc into @student name,@marks
close cc
Deal-locate cctxtPost_CommentEmoticon(':-)');
June 18, 2013 at 8:24 am
mahavidhya24 (6/17/2013)
create table marks(
student name nvar char(50),
marks int
)
Insert into marks(student name,marks) values('ma ha',67)
Insert into marks(student name,marks) values('tuba',78)
Insert into marks(student name,marks) values('Deena',70)
Insert into marks(student name,marks) values('Oriya',90)
Insert into marks(student name,marks) values('saga',98)
select * from marks
Declare @student name nvar char(50)
Declare @marks-2 int
Declare cc cursor
for
select student name,marks from marks
Group by student name,marks
Having Count(1) > 1
open cc
fetch next from cc into @student name,@marks
While @@FETCH_STATUS = 0
begin
--Declare @student name nvar char(50)
--Declare @marks-2 int
select top 3 @marks-2=marks from marks where @student name=student name order by marks desc
end
fetch next from cc into @student name,@marks
close cc
Deal-locate cctxtPost_CommentEmoticon(':-)');
The previous post is a LOT simpler and doesn't have that nasty cursor. There is really no need for a cursor for such a simple process.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
June 18, 2013 at 1:52 pm
Hi
How do you want to deal with records that have the same marks?
For Example
SELECT *
FROM (VALUES
(99, 'Jim')
,(99, 'Joe')
,(97, 'Jock')
,(97, 'John')
,(96, 'Jack')
,(96, 'James')
,(96, 'Jeremy')
,(50, 'Jeb')
) m (Mark, Student)
Would you remove the TOP 3 records (Jim, Joe and Jock)?
Would you remove the top 3 ranked records (Jim, Joe, Jock and John)?
Or would you remove the top 3 scores, everyone except Jeb?
The following should give to a means to do any of the above
WITH ranked AS
(
SELECT Mark, Student
,ROW_NUMBER() OVER (ORDER BY Mark DESC, Student) RowNumber
,RANK() OVER (ORDER BY Mark DESC) Rank
,DENSE_RANK() OVER (ORDER BY Mark DESC) DenseRank
FROM Marks
)
--SELECT *
DELETE
FROM ranked
where Rank <= 3 --Choose which delete you want to do;
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply