Reputation: 394
I have an SQL query that returns a number of records grouped as below:
ID USERNAME PASSWORD IDGROUP LAST_LOGIN
1 1 XXX 999989 27/03/2019 3:55:28 PM
2 2 XXX 999989 27/03/2019 3:55:28 PM
3 3 XXX 999989 27/03/2019 3:55:28 PM
4 4 XXX 999977 27/03/2019 3:55:28 PM
5 5 XXX 999977 27/03/2019 3:55:28 PM
6 6 XXX 999976 27/03/2019 3:55:28 PM
7 7 XXX 999976 27/03/2019 3:55:28 PM
8 8 XXX 682849 17/05/2019 12:23:32 PM
9 9 XXX 682849 17/05/2019 12:23:32 PM
10 10 XXX 682849 28/05/2019 6:56:52 PM
EDIT: The query giving above record is:
SELECT *
FROM schemaN.tableX
WHERE IDGROUP NOT IN (SELECT DISTINCT IDGROUP
FROM (SELECT IDGROUP,COUNT (IDGROUP)OVER (PARTITION BY IDGROUP) cnt
FROM schemaN.tableX
) WHERE CNT=1)
ORDER BY IDGROUP DESC;
The last_login column was recently added and an error occurred where during column creation, the datetime 27/03/2019 3:55:28 PM was inserted for all records as shown above.
In any IDGROUP, only a single user should have access to login. i.e, for ID 999989, we cannot have all the usernames 1,2,3. We should only have one. I don't have a way to know which username is the right one, so, I want to check the username that logged in last and delete all other users for that IDGROUP. i.e For IDGROUP 682849, username 10 will be left and users 8 and 9 will be deleted.
But for IDGROUP like 999989 or 999977 or 999976 which have similar LAST_LOGIN, I want to check IDGROUP with only 2 usernames and only delete one random entry, for any other IDGROUP where users are more than 2, and LAST_LOGIN is same, delete all the records for that IDGROUP from this table, e.g delete all records with IDGROUP 999989 but delete only a single random record for IDGROUP 999977,999976.
The above table is a query result and has huge records, more than 10k records. The db is oracle 12c.
Upvotes: 1
Views: 758
Reputation: 100
This is the list of last logins for each idgroup:
select idgroup, max(last_login) as last_login
from tableX
group by idgroup;
This is the list of records for users who are alone in having the last login for that idgroup:
select max(username) as max_user, idgroup, last_login
from tablex
where (idgroup, last_login) in
(select idgroup, max(last_login) as last_login
from tableX
group by idgroup)
group by idgroup, last_login
having count(distinct username)=1;
So as I understand it, what you want to do is delete all the other records:
delete from tablex where (username, idgroup, last_login) not in (
select max(username) as max_user, idgroup, last_login
from tablex
where (idgroup, last_login) in
(select idgroup, max(last_login) as last_login
from tableX
group by idgroup)
group by idgroup, last_login
having count(distinct username)=1
);
Upvotes: 2