Can I Delete top * 1 from... ? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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)

Can I Delete top * 1 from... ?

Author

Topic

drewsalem
Constraint Violating Yak Guru

304 Posts

Posted - 2006-04-28 : 11:37:29

How do I delete top *1, in the same way one selects top * 1?

Thanks

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-04-28 : 11:43:30

[code]delete t
from yourtable t
where pk = (select top 1 pk from yourtable order by . . . )[/code]

KH

Vignesh
Starting Member

20 Posts

Posted - 2006-04-28 : 11:58:33

Will set rowcount work in this case

drewsalem
Constraint Violating Yak Guru

304 Posts

Posted - 2006-04-28 : 12:11:03

Ah yes, but you see pk is not a primary key. There is more than 1.

There are duplicate account numbers. I want to delete the top row of the pair of rows that contain the duplicate account numbers.

Delete table_name
Where accountNo =
(select top 1 accountNo from table_name
group by accountNo having count(*)>1)

This of course is deleting both rows. I need to delete just the top.

Thanks.

nathans
Aged Yak Warrior

938 Posts

Posted - 2006-04-28 : 12:15:50

yes, ROWCOUNT could be applied here.

set rowcount 1

delete ...

set rowcount 0

Nathan Skerl

Srinika
Master Smack Fu Yak Hacker

1378 Posts

Posted - 2006-04-28 : 12:38:46

drewsalem
U have to use Order By, for the top 1 to be meaningful. Otherwise the Top 1 u think may not be the same as SQL server thinks.

Srinika

drewsalem
Constraint Violating Yak Guru

304 Posts

Posted - 2006-04-28 : 12:51:01

You are a smart lot, aren't you!

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-05-02 : 02:17:09

Yes using Top with Order by is smarter method

Madhivanan

Failing to plan is Planning to fail

sera
Starting Member

1 Post

Posted - 2009-03-31 : 16:32:45

Delete Top (1) From Table
Where Value = 'something'

If you have duplicate entries in your table then you don't need an order by.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2009-03-31 : 17:13:02

Not sure why you'd respond to a topic that is three years old and wouldn't work in SQL 2000 anyway!

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

"Let's begin with the premise that everything you've done up until this point is wrong."

maeenul
Starting Member

20 Posts

Posted - 2009-04-02 : 09:18:29

If you dont mind, I would like to say that, 3 years old post is not any problem. If you have a better solution that will work for newer version of sql server then please reply without thinking the age of the topic. Cause it will help someone like me.

maeenul

brigzy
Starting Member

2 Posts

Posted - 2009-12-02 : 07:46:20

Yep tkizer you think a post should never be updated!

How silly!

Useful update!
Thanks

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-12-02 : 07:51:21

quote:
Originally posted by brigzy

Yep tkizer you think a post should never be updated!

How silly!

Useful update!
Thanks

Any topic can be updated provided that the answer given is correct

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources