InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comThu, 23 May 2013 18:07:05 GMT20http://www.sqlservercentral.com/Forums/Topic1394128-392-1.aspx[quote][b]robert.gerald.taylor (12/9/2012)[/b][hr][quote][b]Lynn Pettis (12/8/2012)[/b][hr][quote][b]CELKO (12/8/2012)[/b][hr][quote] A lot of critics, but thanks for that! Will try to use your suggestions in my following posts. [/quote]Getting toasted by me is a ritual of passage in SQL forums ;-)[/quote]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.[/quote] +1[/quote]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..Tue, 11 Dec 2012 21:38:25 GMTJosh Ashwoodhttp://www.sqlservercentral.com/Forums/Topic1394128-392-1.aspx[quote][b]CELKO (12/8/2012)[/b][hr][quote] A lot of critics, but thanks for that! Will try to use your suggestions in my following posts. [/quote]Getting toasted by me is a ritual of passage in SQL forums ;-)[/quote]I think you must've missed me, but then again I don't post many questions. :-PMon, 10 Dec 2012 17:47:08 GMTdwain.chttp://www.sqlservercentral.com/Forums/Topic1394128-392-1.aspxI would think the old, trusty MAX() would be clearer and less overhead than the mirrored function:[code="sql"]DELETE FROM tnFROM dbo.tablename tnINNER 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[/code]Mon, 10 Dec 2012 16:12:27 GMTScottPletcherhttp://www.sqlservercentral.com/Forums/Topic1394128-392-1.aspx[quote][b]Lynn Pettis (12/8/2012)[/b][hr][quote][b]CELKO (12/8/2012)[/b][hr][quote] A lot of critics, but thanks for that! Will try to use your suggestions in my following posts. [/quote]Getting toasted by me is a ritual of passage in SQL forums ;-)[/quote]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.[/quote] +1Sun, 09 Dec 2012 20:54:53 GMTrobert.gerald.taylorhttp://www.sqlservercentral.com/Forums/Topic1394128-392-1.aspx[quote][b]CELKO (12/8/2012)[/b][hr][quote] A lot of critics, but thanks for that! Will try to use your suggestions in my following posts. [/quote]Getting toasted by me is a ritual of passage in SQL forums ;-)[/quote]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.Sat, 08 Dec 2012 20:43:59 GMTLynn Pettishttp://www.sqlservercentral.com/Forums/Topic1394128-392-1.aspx[quote] A lot of critics, but thanks for that! Will try to use your suggestions in my following posts. [/quote]Getting toasted by me is a ritual of passage in SQL forums ;-)Sat, 08 Dec 2012 19:57:24 GMTCELKOhttp://www.sqlservercentral.com/Forums/Topic1394128-392-1.aspxHi Celko,A lot of critics, but thanks for that! Will try to use your suggestions in my following posts.Cheers,MikeSat, 08 Dec 2012 17:52:24 GMTMike Saunders NLhttp://www.sqlservercentral.com/Forums/Topic1394128-392-1.aspxPlease 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. [quote] 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. [/quote]DELETE FROM LibraryWHERE isbn NOT IN (SELECT MAX(isbn) FROM Library GROUP BY san);This smells like a homework assignment.Sat, 08 Dec 2012 16:25:02 GMTCELKOhttp://www.sqlservercentral.com/Forums/Topic1394128-392-1.aspxThanks Imex, This rocks!Sat, 08 Dec 2012 02:17:36 GMTMike Saunders NLhttp://www.sqlservercentral.com/Forums/Topic1394128-392-1.aspxHi,Try:[code="sql"]with CTE as( select bookid, ROW_NUMBER() OVER(PARTITION BY Location ORDER BY bookid DESC) as RowNum from MyTable)delete from MyTablefrom MyTable as tjoin CTE as c on c.bookid = t.bookid and c.RowNum > 1[/code]Hope this helps.Fri, 07 Dec 2012 09:43:34 GMTimexhttp://www.sqlservercentral.com/Forums/Topic1394128-392-1.aspxHello all,I think that my question is quit simple, but hope someone can help me.I have a table that looks like:Location, bookid, bookname1, 100, Hello World1, 101, Another Book1, 102, Bookstores2, 103, Man on the Phone2, 104, Cup of Soup Story3, 105, Machine TerrorNow 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, Bookstores2, 104, Cup of Soup Story3, 105, Machine TerrorI can’t find out how to make my DELETE query…please helpThanks MikeFri, 07 Dec 2012 09:29:02 GMTMike Saunders NL