need help to remove duplicate rows

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

SQL Server 2008 Forums

Transact-SQL (2008)

need help to remove duplicate rows

Author

Topic

Delinda
Constraint Violating Yak Guru

315 Posts

Posted - 2012-05-11 : 13:54:48

Hi,

I've table as following,


declare @t1 table (applicantIdx int, kursusIdx int, subjectCd varchar(50), merit tinyint)
/*
Combination of applicantIdx, kursusIdx, and subjectCd is UNIQUE
*/

The data in @t1 as following,


applicantIdx	kursusIdx	subjectCd	merit
-2147454057	209	16	8
-2147454057	209	16	8
-2147454057	225	16	8
-2147454057	230	16	8
-2147454057	237	16	8
-2147454057	242	16	8

How to remove the duplicate rows? So the result as following,


applicantIdx	kursusIdx	subjectCd	merit
-2147454057	209	16	8
-2147454057	225	16	8
-2147454057	230	16	8
-2147454057	237	16	8
-2147454057	242	16	8

Sachin.Nand

2937 Posts

Posted - 2012-05-11 : 13:59:04

Please refer to the link below

http://lmgtfy.com/?q=how+to+delete+duplicate+rows+sql+server

After Monday and Tuesday even the calendar says W T F ....

Delinda
Constraint Violating Yak Guru

315 Posts

Posted - 2012-05-11 : 14:11:37

How to SQL to display as follow,

applicantIdx kursusIdx subjectCd merit
-2147454057 209 16 8
-2147454057 225 16 8
-2147454057 230 16 8
-2147454057 237 16 8
-2147454057 242 16 8

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-05-11 : 14:24:12

Many different ways to do it. Here are couple of those:

SELECT applicantIdx, kursusIdx, subjectCd, merit 
FROM @t1
GROUP BY applicantIdx, kursusIdx, subjectCd, merit;

SELECT  applicantIdx, kursusIdx, subjectCd, merit
FROM
(
	SELECT *,
		ROW_NUMBER() OVER 
		(PARTITION BY applicantIdx, kursusIdx, subjectCd, merit ORDER BY (SELECT NULL)) AS RN
	FROM @t1
)s
WHERE RN = 1;

Delinda
Constraint Violating Yak Guru

315 Posts

Posted - 2012-05-11 : 14:29:59

tq sunita

vijays3
Constraint Violating Yak Guru

354 Posts

Posted - 2012-05-12 : 09:30:18

[code]One More way

SELECT DISTINCT applicantIdx,kursusIdx,subjectCd,merit FROM YourTable
[/code]

Vijay is here to learn something from you guys.

vijays3
Constraint Violating Yak Guru

354 Posts

Posted - 2012-05-12 : 09:34:23

[code]Another way

;with cte as

(
SELECT *,
ROW_NUMBER() OVER
(PARTITION BY applicantIdx, kursusIdx, subjectCd, merit ORDER BY (SELECT NULL)) AS RN
FROM #TEMP
)
delete from cte WHERE RN > 1;[/code]

Vijay is here to learn something from you guys.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources