So i am having an issue regarding a query to get the latest data based on status

For example. I have a table like these

**Schema (PostgreSQL v12)**

```
CREATE TABLE test(
  id INTEGER,
  user_id INTEGER,
  product VARCHAR(20),
  status VARCHAR(20)
  );

INSERT INTO test VALUES(1,1,'WALK','ACTIVE');
INSERT INTO test VALUES(2,1,'RUN','ACTIVE');
INSERT INTO test VALUES(3,2,'WALK','INACTIVE');
INSERT INTO test VALUES(4,2,'RUN','ACTIVE');
INSERT INTO test VALUES(5,3,'WALK','UPDATING');
INSERT INTO test VALUES(6,3,'RUN','ACTIVE');
INSERT INTO test VALUES(7,4,'WALK','UPDATING');
INSERT INTO test VALUES(8,4,'RUN','INACTIVE');
INSERT INTO test VALUES(9,2,'RUN','UPDATING');

```

---

**Query #1**

```
SELECT * FROM test;

```

| id | user\_id | product | status |
| --- | --- | --- | --- |
| 1 | 1 | WALK | ACTIVE |
| 2 | 1 | RUN | ACTIVE |
| 3 | 2 | WALK | INACTIVE |
| 4 | 2 | RUN | ACTIVE |
| 5 | 3 | WALK | UPDATING |
| 6 | 3 | RUN | ACTIVE |
| 7 | 4 | WALK | UPDATING |
| 8 | 4 | RUN | INACTIVE |
| 9 | 2 | RUN | UPDATING |

---

[View on DB Fiddle](https://www.db-fiddle.com/f/iwxe23ngagwq9xKWQa1icX/0)

Basically, i need a query to get the latest of user\_id status and the status only for active and updating. But, if active and updating is exists, they pick the active one.
So from this table, the result should be

| user\_id | status |
| --- | --- |
| 1 | ACTIVE |
| 2 | ACTIVE |
| 3 | ACTIVE |
| 4 | UPDATING |

i tried this, but it only return the latest value

```
SELECT distinct on (user_id) user_id, status
FROM test
ORDER BY user_id DESC

```