SQL - SELECTING RECORDS USING MAX

ACCESS 2000.
I have a table(SALEDATE) contains SALESNUMBER,SALESDATE. Each SALESNUMBER has multiple dates. I want to delete all dates associcated with a SALESNUMBER except the earliest date. Ex:
SALESNUMBER 23466 1/2/2003
                      23466 1/9/2003
                      23466 1/16/2003
I want to keep 1/2/2003. I tried to use MAX unsuccessfully. Any help would be appreciated.
dxrodelaAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

mlmccCommented:
I assume by having multiple dates you mean it has multiple records.

Try
Select SALESNUMBER, min(SALESDATE) as MinSaleDate from SALEDATE Group By SALESNUMBER

or
Select TOP 1 SALESNUMBER, SALESDATE from SALEDATE
GROUP BY SALESNUMBER
ORDER BY SALESDATE ASC

mlmcc
0
merphleCommented:
If you want to actually delete the rows from your table:

delete from SALEDATE sd1
where sd1.SALESDATE <> (select min(sd2.SALESDATE) from SALEDATE sd2 where sd2.SALESNUMBER = sd1.SALESNUMBER)
0
Thandava VallepalliCommented:
Hello,

Try this,


DELETE FROM SALEDATE
FROM SALEDATE T1
WHERE SALESDATE   <>  (   SELECT MIN( SALESDATE )
                FROM SALEDATE
                WHERE T1.SALESNUMBER = SALESNUMBER )

V.Thandava Krishna.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Mustak_ShaikhCommented:
dxrodela,

The following query will definitely work:

delete from saledate
where salesnumber&'|'&salesdate
not in (select salesnumber&'|'&min(salesdate) from saledate group by salesnumber)


Mustak_Shaikh
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Programming

From novice to tech pro — start learning today.

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.