Solved

SQL - SELECTING RECORDS USING MAX

Posted on 2003-11-07
4
283 Views
Last Modified: 2010-04-17
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.
0
Comment
Question by:dxrodela
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
4 Comments
 
LVL 101

Expert Comment

by:mlmcc
ID: 9703011
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
 
LVL 3

Expert Comment

by:merphle
ID: 9703485
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
 
LVL 14

Accepted Solution

by:
Thandava Vallepalli earned 125 total points
ID: 9705988
Hello,

Try this,


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

V.Thandava Krishna.
0
 
LVL 1

Expert Comment

by:Mustak_Shaikh
ID: 9710724
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

Featured Post

Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

Question has a verified solution.

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

Whether you’re a college noob or a soon-to-be pro, these tips are sure to help you in your journey to becoming a programming ninja and stand out from the crowd.
Q&A with Course Creator, Mark Lassoff, on the importance of HTML5 in the career of a modern-day developer.
Viewers will learn how to properly install Eclipse with the necessary JDK, and will take a look at an introductory Java program. Download Eclipse installation zip file: Extract files from zip file: Download and install JDK 8: Open Eclipse and …
Six Sigma Control Plans

632 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