sbornstein2
asked on
SQL Query syntax question
Hello all,
I am trying to clean up a table that was bulk loaded with a bunch of duplicate records and I wanted to only keep the MAX(DATEOFFERRED) of the grouped records in the query below. The problem is the MAX(DATE_OFFERRED) has multiple of the same DATEOFFERRED date. So what I want to do is only keep the first of the max date offerred grouped records. Can anyone assist with this?
SELET count(*) FROM Availables t
JOIN
(
SELECT
Part_No
,Qty
,Price
,MAX(DateOfferred) [Latest Date Offerred]
FROM
Availables
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.DateOfferred <> Latest.[Latest Date Offerred]
I am trying to clean up a table that was bulk loaded with a bunch of duplicate records and I wanted to only keep the MAX(DATEOFFERRED) of the grouped records in the query below. The problem is the MAX(DATE_OFFERRED) has multiple of the same DATEOFFERRED date. So what I want to do is only keep the first of the max date offerred grouped records. Can anyone assist with this?
SELET count(*) FROM Availables t
JOIN
(
SELECT
Part_No
,Qty
,Price
,MAX(DateOfferred) [Latest Date Offerred]
FROM
Availables
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.DateOfferred <> Latest.[Latest Date Offerred]
appari
what is your sqlserver version?
ASKER
SQL 2005 for this server
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
How do I get a count of everything but the max records as I want to get an idea how many I can delete. This is my latest query I had to add more fields for the grouping,
Select count(*) from (
Select *, row_number() over(partition by Part_No, MF, DC, Qty, Price, CO_ID order by DateOfferred desc) rowID
FROM Availables) A where A.rowID = 1
Select count(*) from (
Select *, row_number() over(partition by Part_No, MF, DC, Qty, Price, CO_ID order by DateOfferred desc) rowID
FROM Availables) A where A.rowID = 1
ASKER
So my goal it to get a count of the records I will end up deleting then run a delete statement to delete all the non row desc records. I only want to delete of course the records there is more than one though as well so if there is only one record I don't want to delete those.
ASKER
thanks this worked well