The not "Max" records

[Iram]

Hello,
I have a query that finds all records with a particular "Case#". I changed
the query to do totals and grouped by Case#'s. I have another field called
"PrisonID" in which I want to see all records except for the Max (except the
highest number PrisonID). Eventually I need to change this query into a
delete query so that it automatically deletes all duplicate records with the
lowest PrisonID.
Can you help me figure this out?

Thanks.
Iram/mcp

 

[John Spencer]

Are you saying you want to delete all records for each case EXCEPT for the one
with maximum PrisonID?

STEP 1: BACKUP your data before attempting the following.
STEP 2: BACKUP your data before attempting the following.

Without a backup you cannot restore the data if this does not work the way you
expect.

The following could be slow

DELETE
FROM SomeTable
WHERE PrisonID <>
(SELECT MAX(PrisonID) FROM SomeTable as T
WHERE T.[Case#] = SomeTable.[Case#])

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County