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 12»»

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
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, February 27, 2013 6:54 AM
Points: 40, Visits: 168
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 4:25 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Friday, September 19, 2014 5:15 PM
Points: 1,945, Visits: 3,008
Please post DDL, so that people do not have to guess what the keys, constraints, Declarative Referential Integrity, data types, etc. in your schema are. If you know how, follow ISO-11179 data element naming conventions and formatting rules (you do not). Temporal data should use ISO-8601 formats. Code should be in Standard SQL as much as possible and not local dialect.

This is minimal polite behavior on SQL forums. We do not even have a table name! Your terminology is wrong and you clearly did no research on your problem. The book industry uses a SAN (Standard Address Number) for locations and ISBN for books identifiers. Rows are not anything like records. DELETE FROM is not a query.

CREATE TABLE Library
(isbn CHAR(13) NOT NULL PRIMARY KEY
book_title VARCHAR (50) NOT NULL,
san CHAR(10) NOT NULL);

I left out the needed constraints so you can research them and learn how to do DDL correctly. I also ignored the fact that this is not normalized.

Now I want to group by location and then delete all records [sic] in that group besides the record [sic] with the highest id [sic: ISBN] in that group.


DELETE FROM Library
WHERE isbn
NOT IN
(SELECT MAX(isbn) FROM Library GROUP BY san);

This smells like a homework assignment.


Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL
Post #1394364
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 7:57 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Friday, September 19, 2014 5:15 PM
Points: 1,945, Visits: 3,008
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


Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL
Post #1394372
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 @ 4:34 PM
Points: 20,676, Visits: 32,269
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: Friday, September 19, 2014 11:17 AM
Points: 1,184, Visits: 1,220
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
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, September 19, 2014 3:14 PM
Points: 2,098, Visits: 3,155
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)

"We came in spastic, Like tameless horses /
We left in plastic, As numbered corpses / ...
Remember Charlie, Remember Baker /
They left their childhood On every acre /
And who was wrong? And who was right? /
It didn't matter in the thick of the fight." : the inimitable Mr. Billy Joel, about the Vietnam War
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 @ 10:27 PM
Points: 3,417, Visits: 5,331
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!
Post #1394831
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse