delete oldest dates

  • Thread starter Thread starter Bart
  • Start date Start date
B

Bart

I have a table that looks like this

Account Number Creation Date Status
01-029-009 10/23/1992 A
01-029-009 10/20/2000 A
01-029-009 6/13/2001 A
01-035-123 8/15/1998 I
01-035-123 11/25/2004 A

I need to find all duplicate Account Numbers and delete all except the
latest Creation Date.

thanks
 
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Before trying this solution backup your table (just in case ;-) ), or
substitute SELECT for DELETE. The SELECT will show all records that
will be deleted. Be sure to substitute your table name for "Accounts."
Do not remove the alias "A."

DELETE *
FROM Accounts AS A
WHERE A.[Creation Date]<>(SELECT Max([Creation Date]) FROM Accounts
WHERE [Account Number] = A.[Account Number])

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQ9/gCIechKqOuFEgEQLHdQCdFat13MJ79g9o8miIUkrL7WA6fjkAoL6m
rL26pReAOfFAXT1znJfb7ncY
=+NEU
-----END PGP SIGNATURE-----
 
I'm getting an "Enter Parameter Value" for Accounts.Map Number




MGFoster said:
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Before trying this solution backup your table (just in case ;-) ), or
substitute SELECT for DELETE. The SELECT will show all records that
will be deleted. Be sure to substitute your table name for "Accounts."
Do not remove the alias "A."

DELETE *
FROM Accounts AS A
WHERE A.[Creation Date]<>(SELECT Max([Creation Date]) FROM Accounts
WHERE [Account Number] = A.[Account Number])

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQ9/gCIechKqOuFEgEQLHdQCdFat13MJ79g9o8miIUkrL7WA6fjkAoL6m
rL26pReAOfFAXT1znJfb7ncY
=+NEU
-----END PGP SIGNATURE-----

I have a table that looks like this

Account Number Creation Date Status
01-029-009 10/23/1992 A
01-029-009 10/20/2000 A
01-029-009 6/13/2001 A
01-035-123 8/15/1998 I
01-035-123 11/25/2004 A

I need to find all duplicate Account Numbers and delete all except the
latest Creation Date.
Click to expand...
Click to expand...
 
What's is Map Number? It wasn't in the original post. Post your SQL.
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
I'm getting an "Enter Parameter Value" for Accounts.Map Number




-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Before trying this solution backup your table (just in case ;-) ), or
substitute SELECT for DELETE. The SELECT will show all records that
will be deleted. Be sure to substitute your table name for "Accounts."
Do not remove the alias "A."

DELETE *
FROM Accounts AS A
WHERE A.[Creation Date]<>(SELECT Max([Creation Date]) FROM Accounts
WHERE [Account Number] = A.[Account Number])

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQ9/gCIechKqOuFEgEQLHdQCdFat13MJ79g9o8miIUkrL7WA6fjkAoL6m
rL26pReAOfFAXT1znJfb7ncY
=+NEU
-----END PGP SIGNATURE-----

I have a table that looks like this

Account Number Creation Date Status
01-029-009 10/23/1992 A
01-029-009 10/20/2000 A
01-029-009 6/13/2001 A
01-035-123 8/15/1998 I
01-035-123 11/25/2004 A

I need to find all duplicate Account Numbers and delete all except the
latest Creation Date.
Click to expand...
Click to expand...
Click to expand...
 
Sorry, I mean Account Number and I found my error.

thanks for your help, it worked great


MGFoster said:
What's is Map Number? It wasn't in the original post. Post your SQL.
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
I'm getting an "Enter Parameter Value" for Accounts.Map Number




-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Before trying this solution backup your table (just in case ;-) ), or
substitute SELECT for DELETE. The SELECT will show all records that
will be deleted. Be sure to substitute your table name for "Accounts."
Do not remove the alias "A."

DELETE *
FROM Accounts AS A
WHERE A.[Creation Date]<>(SELECT Max([Creation Date]) FROM Accounts
WHERE [Account Number] = A.[Account Number])

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQ9/gCIechKqOuFEgEQLHdQCdFat13MJ79g9o8miIUkrL7WA6fjkAoL6m
rL26pReAOfFAXT1znJfb7ncY
=+NEU
-----END PGP SIGNATURE-----


Bart <bbailey@ wrote:

I have a table that looks like this

Account Number Creation Date Status
01-029-009 10/23/1992 A
01-029-009 10/20/2000 A
01-029-009 6/13/2001 A
01-035-123 8/15/1998 I
01-035-123 11/25/2004 A

I need to find all duplicate Account Numbers and delete all except the
latest Creation Date.
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Sequence number 9
Oldest date 2
Delete query 2
Group By, Max & Last 2
Count records in 10 minute time period 4
Query, Ranges time group by concept 1
Grouping Records 2
Excel date intervals look up 1

Back
Top