SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
 Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 Transact-SQL (2000)
 Can I Delete top * 1 from... ?		  New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

drewsalem
Constraint Violating Yak Guru

304 Posts
Posted - 04/28/2006 :  11:37:29  Show Profile  Reply with Quote

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


Thanks

khtan
In (Som, Ni, Yak)

Singapore
16746 Posts
Posted - 04/28/2006 :  11:43:30  Show Profile  Reply with Quote 
delete t
from   yourtable t
where  pk = (select top 1 pk from yourtable order by . . . )



KH
Go to Top of Page

Vignesh
Starting Member

India
20 Posts
Posted - 04/28/2006 :  11:58:33  Show Profile  Click to see Vignesh's MSN Messenger address  Send Vignesh a Yahoo! Message  Reply with Quote
Will set rowcount work in this case
Go to Top of Page

drewsalem
Constraint Violating Yak Guru

304 Posts
Posted - 04/28/2006 :  12:11:03  Show Profile  Reply with Quote
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.
Go to Top of Page

nathans
Aged Yak Warrior

USA
933 Posts
Posted - 04/28/2006 :  12:15:50  Show Profile  Reply with Quote
yes, ROWCOUNT could be applied here.

set rowcount 1

delete ...

set rowcount 0



Nathan Skerl
Go to Top of Page

Srinika
Flowing Fount of Yak Knowledge

Sri Lanka
1378 Posts
Posted - 04/28/2006 :  12:38:46  Show Profile  Reply with Quote
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
Go to Top of Page

drewsalem
Constraint Violating Yak Guru

304 Posts
Posted - 04/28/2006 :  12:51:01  Show Profile  Reply with Quote
You are a smart lot, aren't you!
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22461 Posts
Posted - 05/02/2006 :  02:17:09  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
Yes using Top with Order by is smarter method

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

sera
Starting Member

1 Posts
Posted - 03/31/2009 :  16:32:45  Show Profile  Reply with Quote
Delete Top (1) From Table
Where Value = 'something'

If you have duplicate entries in your table then you don't need an order by.
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
35007 Posts
Posted - 03/31/2009 :  17:13:02  Show Profile  Visit tkizer's Homepage  Reply with Quote
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."
Edited by - tkizer on 03/31/2009 17:13:24
Go to Top of Page

maeenul
Starting Member

20 Posts
Posted - 04/02/2009 :  09:18:29  Show Profile  Reply with Quote
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
Go to Top of Page

brigzy
Starting Member

2 Posts
Posted - 12/02/2009 :  07:46:20  Show Profile  Reply with Quote
Yep tkizer you think a post should never be updated!

How silly!

Useful update!
Thanks
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22461 Posts
Posted - 12/02/2009 :  07:51:21  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
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
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000