CODEWITHSUNDEEP
sqloracle-database
kmihingo
kmihingo

Reputation: 394

SQL Delete records from grouped rows with condition

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

Answers (1)

Heiner
Heiner

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

Related Questions