How to filter numbers who permanently left the group using SQL?-CodePudding

| Number | Status       | Time                       |
|--------|--------------|----------------------------|
| 2132   | Group Joined | August 22, 2022, 2:54 PM   |
| 3299   | Group Joined | August 12, 2022, 9:24 PM   |
| 3299   | Group Left   | August 23, 2022, 5:43 PM   |
| 2132   | Group Left   | August 30, 2022, 5:32 PM   |
| 2132   | Group Joined | September 3, 2022, 2:23 PM |

Here's a table which has all the details, and I only need numbers who permanently left the group, here you can see I need number 3299 because that number never rejoined the group but I don't need 2132 as that number has joined the group again!

I thought about working with time and sort it and make some query but I don't know how am I supposed write the query.

CodePudding user response：

Assuming your time column be a bona fide datetime or timestamp column, we can use exists logic here:

SELECT *
FROM yourTable t1
WHERE Status = 'Group Left' AND
      NOT EXISTS (
          SELECT 1
          FROM yourTable t2
          WHERE t2.Number = t1.Number AND
                t2.time > t1.time
      );

In plain English, the above query says to find numbers whose status is "Group Left" and who never have any newer activity after leaving.