Delete records with dates less than the max date

[ramindya]

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 !!

[pbaldy]

Does this help?

http://www.baldyweb.com/LastValue.htm

[ramindya]

SELECT * FROM HEALTH_HABITS_RAM
WHERE DateCompleteSurvey = ( SELECT MAX(DateCompleteSurvey) FROM HEALTH_HABITS_RAM as TEST3
WHERE HEALTH_HABITS_RAM.MRN = TEST3.MRN)
ORDER BY HEALTH_HABITS_RAM.MRN


DELETE * FROM HEALTH_HABITS_RAM
WHERE DateCompleteSurvey < ( SELECT MAX(DateCompleteSurvey) FROM HEALTH_HABITS_RAM as TEST3
WHERE HEALTH_HABITS_RAM.MRN = TEST3.MRN)


SELECT * INTO OLD_TEST FROM HEALTH_HABITS_RAM
WHERE DateCompleteSurvey < ( SELECT MAX(DateCompleteSurvey) FROM HEALTH_HABITS_RAM as TEST3
WHERE HEALTH_HABITS_RAM.MRN = TEST3.MRN)
ORDER BY HEALTH_HABITS_RAM.MRN