dupliacte row problem - 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)

dupliacte row problem

Author

Topic

rajasekhar857
Constraint Violating Yak Guru

396 Posts

Posted - 2009-08-25 : 06:00:51

hi iam having a table like x

where record are like this

a b c
100.81 1567 1553
100.81 1772 1772
100.81 1772 1831
100.81 1782 1774

there are so many records in it like this iam giving an example
i want a query where i can delete a record if a and b are repeating twice then we have to consider the maximun one of c.

that means out of this
100.81 1772 1772
100.81 1772 1831

i want only 2 row to be there.

please give a delete query for this.

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2009-08-25 : 06:03:12

select a,b, max(c) c into #tmpa from x group by a,b

truncate table x

insert into x
select a,b,c from #tmpa

drop table #tmpa

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-08-25 : 06:11:27

delete x from tablename x left join (select a,b,max(c) as c from tablename group by a,b)s on s.a = x.a
and s.b = x.b and s.c = x.c where s.c is null

rajasekhar857
Constraint Violating Yak Guru

396 Posts

Posted - 2009-08-25 : 06:12:18

but there are some other values also other than this.those how can i proceed for example

a b c
100.81 1638 1617
100.81 1772 1831
100.81 1649 1657
100.81 1622 1598
100.81 1567 1553
100.81 1772 1772
100.81 1715 1771
100.81 1527 1513
100.81 1782 1774

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-08-25 : 06:21:58

So, you want only one value of b anc c for each a?

Madhivanan

Failing to plan is Planning to fail

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2009-08-25 : 06:26:15

quote:
Originally posted by rajasekhar857

but there are some other values also other than this.those how can i proceed for example

a b c
100.81 1638 1617
100.81 1772 1831
100.81 1649 1657
100.81 1622 1598
100.81 1567 1553
100.81 1772 1772
100.81 1715 1771
100.81 1527 1513
100.81 1782 1774

have you tried running the select part of my query?

rajasekhar857
Constraint Violating Yak Guru

396 Posts

Posted - 2009-08-25 : 06:27:54

no 100.81 1772 1831
100.81 1772 1772 are there out of i want 100.81 1772 1831 because c is max
rest of columns should be there that is i required.
only 100.81 1772 1772 got deleted

Ifor
Aged Yak Warrior

700 Posts

Posted - 2009-08-25 : 06:49:55

[code]
DELETE D
FROM
(
SELECT *
,ROW_NUMBER() OVER (PARTITION BY a, b ORDER BY c DESC) AS RowNum
FROM YourTable
) D
WHERE RowNum > 1
[/code]

rajasekhar857
Constraint Violating Yak Guru

396 Posts

Posted - 2009-08-25 : 06:51:03

actually my table is having 17 columns out of that i have shown 3 columns where i need to delete as above mentioned ones.because a and b columns shoud not be repeated that is the case.so if got repeated i have to consider max value of c and min value shoud be deleted.

rajasekhar857
Constraint Violating Yak Guru

396 Posts

Posted - 2009-08-25 : 06:54:06

DELETE D
FROM
(
SELECT *
,ROW_NUMBER() OVER (PARTITION BY a, b ORDER BY c DESC) AS RowNum
FROM YourTable
) D
WHERE RowNum > 1

HOW CAN I DO THIS IN ORACLE

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-08-25 : 08:22:55

quote:
Originally posted by rajasekhar857

DELETE D
FROM
(
SELECT *
,ROW_NUMBER() OVER (PARTITION BY a, b ORDER BY c DESC) AS RowNum
FROM YourTable
) D
WHERE RowNum > 1

HOW CAN I DO THIS IN ORACLE

POST in Oracle forums such as www.orafaq.com

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources