I got a chain of events per user and need to select an specific event but need to select those who's maximun event is that in specific. Let me explain with a table.

I need to select those who are in the event 3 and haven't pass to the event 4. It is a process in the database so I need those who are stuck in the event 3.

I was trying the fuction rank () but didn't work out.

CodePudding user response：

You can try a query like:

SELECT user
FROM table
WHERE event = 'Event 3'
  AND user NOT IN
    (SELECT user
     FROM table
     WHERE event = 'Event 4');

CodePudding user response：

You can achieve this with DENSE_RANK():

WITH cte AS (SELECT User, Event, 
               DENSE_RANK() OVER (ORDER BY Event ASC) as dr
             FROM test)
SELECT User, Event FROM cte 
  WHERE User NOT IN (SELECT User FROM cte WHERE dr >= 4)
ORDER BY User DESC

Result (using my provided sample data):

| User | Event    |
|------|----------|
| ABC  | Event 1  |
| ABC  | Event 2  |
| ABC  | Event 3  |

If you only want to return the User, add a GROUP BY clause:

WITH cte AS (SELECT User, Event, 
               DENSE_RANK() OVER (ORDER BY Event ASC) as dr
             FROM test)
SELECT User FROM cte 
  WHERE User NOT IN (SELECT User FROM cte WHERE dr >= 4)
GROUP BY User
ORDER BY User DESC

Result:

| User  |
|-------|
| ABC   |

Fiddle here.

Note: If you're using SQL Server, you will have to wrap the keyword User in brackets [User] like so.