Aggregate SQL queries in Access / duplicates that are NOT max 1

[mickeyziggyk]

Greetings

I think I have an interesting question that has me baffled.

It is as so:
I have a relation/table that has the following structure:

ID1 (auto int pkey)
ID (text / char)
Empl_recd_no (int)

There are duplicate entries within the attribute / column 'ID'.
For each duplicate entry there is a differentiator within Empl_recd_no as either 0,1,2 etc etc.

Where ID is unique, that does not mean that Empl_recd_no will be 0, it could be anything.

For each duplicate, the highest number within Empl_rec_no is the tuple / row I want to keep, and the others I want to delete.

e.g

ID EMP_REC_NO
1 0
1 1
1 2

In the above example I would want to keep row (1,2) and delete (1,0) and (1,1).

But, for each duplicate ID, it does not mean that the EMP_REC_NO follows sequentially e.g.

ID EMP_REC_NO
2 0
2 2

I have got as far as pulling through all the duplicates:

SELECT *
FROM AA
WHERE id in
(select id from AA
group by id having count(id)>1);

Now, I want to delete those who are not the MAX EMP_REC_NO.

Access has a brain fart if I try querying within the relation and asking for <> MAX(EMP_REC_NO).

I hope this makes sense, and I know I could easily delete the duplicates in two SQL queries or some other way, but I am consumed with this puzzle and want to do it in one!

Any help would be appreciated

 

[lespaul]

typed, not tested, backup your table first!!

Delete from TableName WHERE ID1 NOT IN(
SELECT ID1 FROM TableName T1
INNER JOIN ([COLOR=red]SELECT ID, MAX(EMP_REC_NO) As MaxRec FROM TableName GROUP BY ID[/color]) T2 on T1.ID = T2.ID And T1.EMP_REC_NO = T2.MaxRec)

the red section gets the max Emp_rec_no for each employee, you then join into that subset to get the ID1 number for each of those records and then you delete all the records that have ID1 that are NOT IN that set.

Make sense?

HTH


Leslie

In an open world there's no need for windows and gates

http://www.ubuntu.com

 

[mickeyziggyk]

Leslie

It makes perfect sense, thank you very much (I did not know you could nest after the join statement, I will start experimenting!)

Michal

 

[mickeyziggyk]

FYI Leslie

I had some trouble with your suggestion so after a little tweaking (and it is great to know that you can nest after the join statement) and I got the code below to delete those tuples / rows that were duplicates and not the maximum emp_rec_no:

DELETE *
FROM Tablename
WHERE id1 in ( select t1.id1 from tablename AS t1 INNER JOIN (select id, max(emp_rec_no) as maxi from tablename group by id) AS t2 ON (t1.id=t2.id) AND (t1.emp_rec_no<t2.maxi));

But again, many thanks for your help

 

[lespaul]

glad to help! thanks for the star....happy friday!

leslie