Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Delete records in group - but not all Expand / Collapse
Author
Message
Posted Friday, December 7, 2012 9:29 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, May 13, 2014 6:31 AM
Points: 23, Visits: 92
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
Post #1394128
Posted Friday, December 7, 2012 9:43 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Thursday, July 23, 2015 11:17 AM
Points: 69, Visits: 199
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.


http://www.imoveisemexposicao.com.br
Post #1394141
Posted Saturday, December 8, 2012 2:17 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, May 13, 2014 6:31 AM
Points: 23, Visits: 92
Thanks Imex, This rocks!
Post #1394314
Posted Saturday, December 8, 2012 5:52 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, May 13, 2014 6:31 AM
Points: 23, Visits: 92
Hi Celko,

A lot of critics, but thanks for that! Will try to use your suggestions in my following posts.

Cheers,
Mike
Post #1394366
Posted Saturday, December 8, 2012 8:43 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 2:00 PM
Points: 22,053, Visits: 34,931
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.



Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #1394373
Posted Sunday, December 9, 2012 8:54 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, August 20, 2015 9:16 AM
Points: 1,270, Visits: 1,344
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
Post #1394437
Posted Monday, December 10, 2012 4:12 PM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 3:56 PM
Points: 3,019, Visits: 4,629
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)

"If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them." James Blackburn, in closing argument in the "Fatal Vision" murders trial
Post #1394807
Posted Monday, December 10, 2012 5:47 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 5:48 PM
Points: 3,971, Visits: 6,355
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 mantra: No loops! No CURSORs! No RBAR! Hoo-uh!

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?
Since random numbers are too important to be left to chance, let's generate some!
Learn to understand recursive CTEs by example.
Splitting strings based on patterns can be fast!
My temporal SQL musings: Calendar Tables, an Easter SQL, Time Slots and Self-maintaining, Contiguous Effective Dates in Temporal Tables
Post #1394831
Posted Tuesday, December 11, 2012 9:38 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, August 25, 2015 9:12 PM
Points: 30, Visits: 535
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..
Post #1395416
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse