[Webinar] Streamline your web hosting managementRegister Today

x
?
Solved

SQL delete syntax with group by

Posted on 2013-01-21
3
Medium Priority
?
292 Views
Last Modified: 2013-01-21
Hello all,

I have a table that has four fields (Part_No, Qty, Price, DateEntered).  I need to group on everything but DateEntered and I want to first get a select so I can run an insert statement into an archive table for all the records 'except' the max date entered for that grouped records.  

Then I need to delete these records so I only end up with the grouped records with the max date entered.

Thanks for any help.
0
Comment
Question by:sbornstein2
3 Comments
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 38801001
you cannot use DELETE and GROUP BY at the same level.

what you can do is to delete all but the one having the MAX() value via subquery:
DELETE t
   FROM yourtable t
  WHERE NOT EXISTS( SELECT NULL FROM yourtable l where l.part_no = t.part_no AND l.dateentered > t.dateentered 

Open in new window


an article about "distinct/group by" is here, it may also help:
http://www.experts-exchange.com/Database/Miscellaneous/A_3203-DISTINCT-vs-GROUP-BY-and-why-does-it-not-work-for-my-query.html
0
 
LVL 17

Accepted Solution

by:
Barry Cunney earned 1500 total points
ID: 38801007
INSERT ArchiveTable
SELET * FROM [YourTable] t
JOIN
(
SELECT
Part_No
,Qty
,Price
,MAX(DateEntered) [Latest Date Entered]
FROM
[YourTable]
GROUP BY
Part_No
,Qty
,Price
) Latest
ON
t.Part_No = Latest.Part_No
AND t.Qty = Latest.Qty
AND t.Price = Latest.Price
AND t.DateEntered <> Latest.[Latest Date Entered]


DELETE t
FROM [YourTable] t
JOIN
(
SELECT
Part_No
,Qty
,Price
,MAX(DateEntered) [Latest Date Entered]
FROM
[YourTable]
GROUP BY
Part_No
,Qty
,Price
) Latest
ON
t.Part_No = Latest.Part_No
AND t.Qty = Latest.Qty
AND t.Price = Latest.Price
AND t.DateEntered <> Latest.[Latest Date Entered]
0
 

Author Closing Comment

by:sbornstein2
ID: 38801140
thanks this should work well
0

Featured Post

The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Viewers will learn how the fundamental information of how to create a table.
Suggested Courses

591 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question