delete min ids - 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 2000 Forums

Transact-SQL (2000)

delete min ids

Author

Topic

arkiboys
Master Smack Fu Yak Hacker

1433 Posts

Posted - 2007-10-10 : 09:24:32

There are duplicates in my table and I am trying to write a sql query to delete the Id column which has the min value and therefore leaving the ID with the max value in the table.
This is what I have but not sure how to complete the where clause in the delete part.

Thanks

delete from tblPricesHistoryCopy
where
????
select
min(HistoryID) as 'HistoryID',
securityname,
closingdate
from
tblPricesHistoryCopy
group by
securityname,
closingdate
having count(closingdate) > 1

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-10-10 : 09:31:25

What if you have THREE records of same kind?
This will delete all records BUT max record.

delete		x
from		tblpriceshistorycopy as x
inner join	(
			select		securityname,
					closingdate,
					max(historyid) as historyid
			from		tblpriceshistorycopy
			group by	securityname,
					closingdate
		) as y on y.securityname = x.securityname
			and y.closingdate = x.closingdate
			and y.historyid > x.historyid

E 12°55'05.25"
N 56°04'39.16"

arkiboys
Master Smack Fu Yak Hacker

1433 Posts

Posted - 2007-10-10 : 09:37:14

This is fine but does your query keep the max or min HistoryID please?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-10-10 : 09:39:14

If keeps the MAX record.
Didn't I write that in last reply? I have to go check again.

E 12°55'05.25"
N 56°04'39.16"

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-10-10 : 09:43:46

This might be helpful for you:

http://weblogs.sqlteam.com/jeffs/archive/2004/10/07/2190.aspx

- Jeff
http://weblogs.sqlteam.com/JeffS

Kristen
Test

22859 Posts

Posted - 2007-10-10 : 09:55:06

"Didn't I write that in last reply? I have to go check again."

You used the double-negative. I'll stick it in my reverse-polish-to-English translator for you

Translate: This will delete all records BUT max record.

Answer: This keeps ONLY the MAX record.

Neat the way the translator re-capitalises the emphasis, eh? I think I might Patent it

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources