How to check duplicate row? - SQL Server Forums

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 2005 Forums

Transact-SQL (2005)

How to check duplicate row?

Author

Topic

Delinda
Constraint Violating Yak Guru

315 Posts

Posted - 2010-02-22 : 10:43:08

My table and rows as follow,

tPGrp
RID | SCout | ECout | Pgrp | SCurr
---------------------------------------------------
_r00000018 BP JB AZRM RM
_r00000035 BP KL AZRM RM
_r00000036 BP KL AZRM RM
_r00000036 BP KLANG AZRM RM
_r00000035 BP KL azsc30RM RM
_r00000018 BP JB azsc30RM RM
_r00000036 BP KL azsc30RM RM
_r00000036 BP KLANG azsc30RM RM
_r00000007 KL JB BCTAN2RM RM
_r00000007 KL SG BCTAN2RM RM
_r00000006 KL JB BCTAN2RM RM
...
...
...
/*Combination of [RID],[Pgrp],[SCout],[ECout] is unique*/

How to query, and resultset will return duplicate row

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-22 : 10:49:21

[code]SELECT columns...
FROM
(
SELECT ROW_NUMBER() OVER(PARTITION BY [Pgrp],[SCout],[ECout] ORDER BY RID DESC) AS Seq,*
FROM Table
)t
WHERE Seq=1
[/code]

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

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-02-22 : 10:51:07

I can see no duplicates in given example.
What should be the output?

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

Delinda
Constraint Violating Yak Guru

315 Posts

Posted - 2010-02-22 : 11:13:05

quote:
Originally posted by visakh16

SELECT columns...
FROM
(
SELECT ROW_NUMBER() OVER(PARTITION BY [Pgrp],[SCout],[ECout] ORDER BY RID DESC) AS Seq,*
FROM Table
)t
WHERE Seq=1

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

declare @tPrice table
(rid varchar(30),scout varchar(30),ecout varchar(30),pgrp varchar(50),scurr varchar(10))
insert into @tPrice
select
RID, SCout, ECout, Pgrp+SCurr as Pgrp, SCurr
from tPDetails
where Stat='D' order by Pgrp,SCurr

SELECT RID, SCout, ECout, Pgrp
FROM
(
SELECT ROW_NUMBER() OVER(PARTITION BY [Pgrp],[SCout],[ECout] ORDER BY RID DESC) AS Seq,*
FROM @tPrice
)t
WHERE Seq=1

this will return,
_r00000018 BP JB AZRM
_r00000036 BP KL AZRM
_r00000036 BP KLANG AZRM
_r00000018 BP JB azsc30RM

but i check in
select * from
@tPrice where rid='_r00000018' and scout='BP'
and ecout='JB' and Pgrp='AZRM'

it return 1 row. there's no duplicate

again, combination of
[RID]
[Pgrp]
[SCout]
[ECout]
is a unique

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-22 : 11:17:13

sorry didnt get you

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

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-02-22 : 11:39:20

again:

quote:
Originally posted by webfred

I can see no duplicates in given example.
What should be the output?

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

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

Delinda
Constraint Violating Yak Guru

315 Posts

Posted - 2010-02-22 : 20:06:01

quote:
Originally posted by visakh16

sorry didnt get you

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

So far, i've as follow,
declare @tPrice table
(rid varchar(30),scout varchar(30),ecout varchar(30),pgrp varchar(50),scurr varchar(10))
insert into @tPrice values('_r00000018','BP','JB','AZRM','RM')
insert into @tPrice values('_r00000017','BP','SG','AZRM','RM')
insert into @tPrice values('_r00000018','BP','JB','AZRM','RM')
insert into @tPrice values('_r00000017','BP','JB','AZRM','RM')
insert into @tPrice values('_r00000013','BP','JB','AZRM','SG')

@tPrice
rid scout ecout pgrp scurr
-------------------------------------------------
_r00000018 BP JB AZRM RM
_r00000017 BP SG AZRM RM
_r00000018 BP JB AZRM RM
_r00000017 BP JB AZRM RM
_r00000013 BP JB AZRM SG
/*combination of rid,scout,ecout,pgrp generating a unique row*/
/*as you can see,
row - _r00000018 BP JB AZRM RM is duplicate
*/

but after i run as follow,
SELECT *
FROM
(
SELECT ROW_NUMBER() OVER(PARTITION BY [Pgrp],[SCout],[ECout] ORDER BY RID DESC) AS Seq,*
FROM @tPrice
)t
WHERE Seq=1

the resultset said, the duplicates was
1 _r00000018 BP JB AZRM RM
1 _r00000017 BP SG AZRM RM

This wrong, because row - _r00000017 BP SG AZRM RM is not duplicate

Sachin.Nand

2937 Posts

Posted - 2010-02-22 : 22:08:12

But in your original data there were no duplicates.So now how do you want the output should be.Just the duplicate records?


SELECT *
FROM
(
SELECT ROW_NUMBER() OVER(PARTITION BY rid,[Pgrp],[SCout],[ECout] ORDER BY RID DESC) AS Seq,*
FROM @tPrice
)t
WHERE Seq<>1

PBUH

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-02-22 : 22:17:23

change to


PARTITION BY [RID],[Pgrp],[SCout],[ECout]

in visakh16's query

KH
[spoiler]Time is always against us[/spoiler]

Delinda
Constraint Violating Yak Guru

315 Posts

Posted - 2010-02-22 : 22:28:17

tq very much. all answers is my inspiration

Sachin.Nand

2937 Posts

Posted - 2010-02-22 : 22:32:17

quote:
Originally posted by Delinda

tq very much. all answers is my inspiration

So which query worked?

PBUH

Delinda
Constraint Violating Yak Guru

315 Posts

Posted - 2010-02-22 : 22:34:20

both,

SELECT *
FROM
(
SELECT ROW_NUMBER() OVER(PARTITION BY RID,[Pgrp],[SCout],[ECout] ORDER BY RID DESC) AS Seq,*
FROM Table
)t
WHERE Seq=1

will return no duplicate row

SELECT *
FROM
(
SELECT ROW_NUMBER() OVER(PARTITION BY rid,[Pgrp],[SCout],[ECout] ORDER BY RID DESC) AS Seq,*
FROM @tPrice
)t
WHERE Seq<>1

will return duplicate row

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources