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]
sbornstein2Asked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
appariConnect With a Mentor Commented:
if your SQL version is above 2005 you can try this

Select * from (
Select *, row_number() over(partition by  Part_No  ,Qty  ,Price order by DateOfferred desc) rowID
 FROM Availables) A where A.rowID = 1

Open in new window

0
 
appariCommented:
what is your sqlserver version?
0
 
sbornstein2Author Commented:
SQL 2005 for this server
0
What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

 
sbornstein2Author Commented:
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
0
 
sbornstein2Author Commented:
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.
0
 
sbornstein2Author Commented:
thanks this worked well
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.