December 7, 2012 at 9:29 am
Hello all,
I think that my question is quit simple, but hope someone can help me.
I have a table that looks like:
Location, bookid, bookname
1, 100, Hello World
1, 101, Another Book
1, 102, Bookstores
2, 103, Man on the Phone
2, 104, Cup of Soup Story
3, 105, Machine Terror
Now I want to group by location and then delete all records in that group besides the record with the highest id in that group.
After deletion my table has to be:
1, 102, Bookstores
2, 104, Cup of Soup Story
3, 105, Machine Terror
I can’t find out how to make my DELETE query…please help
Thanks Mike
December 7, 2012 at 9:43 am
Hi,
Try:
with CTE as
(
select
bookid,
ROW_NUMBER() OVER(PARTITION BY Location ORDER BY bookid DESC) as RowNum
from MyTable
)
delete from MyTable
from MyTable as t
join CTE as c
on c.bookid = t.bookid and c.RowNum > 1
Hope this helps.
December 8, 2012 at 2:17 am
Thanks Imex, This rocks!
December 8, 2012 at 5:52 pm
Hi Celko,
A lot of critics, but thanks for that! Will try to use your suggestions in my following posts.
Cheers,
Mike
December 8, 2012 at 8:43 pm
CELKO (12/8/2012)
A lot of critics, but thanks for that! Will try to use your suggestions in my following posts.
Getting toasted by me is a ritual of passage in SQL forums 😉
And most of us would like to see you stop it also, just like hazing in high school and college it has no place here. It really detracts from what is normally a fairly professional site.
December 9, 2012 at 8:54 pm
Lynn Pettis (12/8/2012)
CELKO (12/8/2012)
A lot of critics, but thanks for that! Will try to use your suggestions in my following posts.
Getting toasted by me is a ritual of passage in SQL forums 😉
And most of us would like to see you stop it also, just like hazing in high school and college it has no place here. It really detracts from what is normally a fairly professional site.
+1
December 10, 2012 at 4:12 pm
I would think the old, trusty MAX() would be clearer and less overhead than the mirrored function:
DELETE FROM tn
FROM dbo.tablename tn
INNER JOIN (
SELECT location, MAX(bookid) AS bookid
FROM dbo.tablename
GROUP BY location
) AS tn_max ON
tn.location = tn_max.location AND
tn.bookid < tn_max.bookid
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
December 10, 2012 at 5:47 pm
CELKO (12/8/2012)
A lot of critics, but thanks for that! Will try to use your suggestions in my following posts.
Getting toasted by me is a ritual of passage in SQL forums 😉
I think you must've missed me, but then again I don't post many questions. 😛
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
December 11, 2012 at 9:38 pm
robert.gerald.taylor (12/9/2012)
Lynn Pettis (12/8/2012)
CELKO (12/8/2012)
A lot of critics, but thanks for that! Will try to use your suggestions in my following posts.
Getting toasted by me is a ritual of passage in SQL forums 😉
And most of us would like to see you stop it also, just like hazing in high school and college it has no place here. It really detracts from what is normally a fairly professional site.
+1
I am all for Celko's ranting.. he never takes it too far and it livens up SQL forums dramatically.
I likely would have forgotten some of the points Celko has made if they weren't done so colorfully.
Keep up the good work Joe!
Now.. back to sorting that deck of punch cards..
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply