mickeyziggyk
Technical User
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
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