Delete duplicate records same table - SQL doubt

ramindya

New member
Local time
Today, 07:00
Joined
Feb 22, 2012
Messages
9
I have a table A that has following fields MRN, DATEOFLASTCONTACT, NAME. There is no primary key in this table.

I want to delete records from the table for the same patient whose date of last contact is less than max date of last contact.
Example:

TABLE A: ( BEFORE RUNNING SQL QUERY )
NAME MRN DATEOFLASTCONTACT
JOHN 1234 12/12/2012
JOHN 1234 12/12/2011
JOHN 1234 11/10/2010

I need the SQL query that can give me the following output in the same table TABLE A:

NAME MRN DATEOFLASTCONTACT

JOHN 1234 12/12/2012

I also want to create a seperate table TABLE B from the intial table TABLE A as:
NAME MRN DATEOFLASTCONTACT
JOHN 1234 12/12/2011
JOHN 1234 11/10/2010

Can you let me know the SQL for above ? Thanks !!
 
You can do this with three Queries with the following steps:

1. Create a Make-Table Query with the following SQL:
Code:
SELECT TABLEA.Nam, TABLEA.MRN, Max(TABLEA.DATEOFLASTCONTACT) AS MaxOfDATEOFLASTCONTACT INTO TABLE_PARAM
FROM TABLEA
GROUP BY TABLEA.Nam, TABLEA.MRN;

The above query will create a table with the name: TABLE_PARAM saving all Unique records with the Maximum value of DATEOFLASTCONTACT. This will exclude records multiple records of the same contact with less than the maximum date.

2. Create a second Query to extract the unwanted records (records with less than maximum date) and save them into TABLEB:
Code:
SELECT TABLEA.Nam, TABLEA.MRN, TABLEA.DATEOFLASTCONTACT INTO TABLE_B
FROM TABLEA INNER JOIN TABLE_PARAM ON (TABLEA.MRN=TABLE_PARAM.MRN) AND (TABLEA.Nam=TABLE_PARAM.Nam)
WHERE (((TABLEA.DATEOFLASTCONTACT)<>[MaxofDATEOFLASTCONTACT]));

3. To delete the records saved in TABLEB from TABLEA run the following Query:
Code:
SELECT TABLEA.Nam, TABLEA.MRN, TABLEA.DATEOFLASTCONTACT INTO TABLE_B
FROM TABLEA INNER JOIN TABLE_PARAM ON (TABLEA.MRN=TABLE_PARAM.MRN) AND (TABLEA.Nam=TABLE_PARAM.Nam)
WHERE (((TABLEA.DATEOFLASTCONTACT)<>[MaxofDATEOFLASTCONTACT]));

NB: Now TABLE_PARAM and TABLEA have the same records. If there are more data fields in TABLEA then modify the Queries to include them too.
 

Users who are viewing this thread

Back
Top Bottom