I've got a long table that tracks a numerical 'state' value (0=new, 1=setup mode, 2=retired, 3=active, 4=inactive) of a collection of 'devices' historically. These devices may be activated/deactivated throughout the year, so the table is continuous collection of state changes - mostly state 3 and 4, ordered by id, with a timestamp on the end, for example:

```
     id    | device_id | new_state |        when         
 ----------+-----------+-----------+----------------------------
 218010581 |      2505 |         0 | 2022-06-06 16:28:11.174084
 218010580 |      2505 |         1 | 2022-06-06 16:28:11.174084
 218010634 |      2505 |         3 | 2022-06-06 16:29:25.129019
 218087737 |       659 |         3 | 2022-06-07 22:55:48.705208
 218087744 |      1392 |         3 | 2022-06-07 22:55:59.016974
 218087757 |      1556 |         3 | 2022-06-07 22:56:09.811876
 218087758 |      2071 |         1 | 2022-06-07 22:56:20.850095
 218087765 |      2071 |         3 | 2022-06-07 22:56:29.122074

```

When I want to look for a list of devices and see their 'history', I know I can use something like:

```
select * 
from devstatechange 
where device_id = 2345 
order by "when";

    id     | device_id | new_state |            when            
-----------+-----------+-----------+----------------------------
 184682659 |      2345 |         0 | 2021-05-27 17:03:36.894429
 184682658 |      2345 |         1 | 2021-05-27 17:03:36.894429
 184684721 |      2345 |         3 | 2021-05-27 17:31:01.968314
 194933399 |      2345 |         4 | 2021-08-31 23:30:05.555407
 195213746 |      2345 |         3 | 2021-09-03 16:53:39.043005
 206278232 |      2345 |         4 | 2021-12-31 22:30:08.820068
 206515355 |      2345 |         3 | 2022-01-03 16:06:01.223759
 215709888 |      2345 |         4 | 2022-04-30 23:30:30.309389
 215846807 |      2345 |         3 | 2022-05-02 19:40:31.525514

select * 
from devstatechange 
where device_id = 2351 
order by "when";

    id     | device_id | new_state |            when            
-----------+-----------+-----------+----------------------------
 186091252 |      2351 |         0 | 2021-06-09 15:36:02.775035
 186091253 |      2351 |         1 | 2021-06-09 15:36:02.775035
 186091349 |      2351 |         3 | 2021-06-09 15:37:56.965599
 197880878 |      2351 |         4 | 2021-09-30 23:30:06.691835
 197945073 |      2351 |         3 | 2021-10-01 15:32:35.907913
 208981857 |      2351 |         4 | 2022-01-31 22:30:09.521694
 209722639 |      2351 |         3 | 2022-02-09 15:20:12.412816
 217666572 |      2351 |         4 | 2022-05-31 23:30:30.881928

```

What I am really looking for is a query that returns a unique list of devices where *the latest dated entry for each device **only** contains a state of '4'* ('inactive state'), and not include records that do not match.

So in using the above data samples, even though both devices 2345 and 2351 have states of 3 and 4 throughout their history, only device 2351 has it's last dated entry with a state of 4 - meaning it is currently in an 'inactive' state. Device 2345's would not appear in the result set since its last dated entry has a state of 3 - it's still active.

Stabbing in the dark, I've tried variants of:

```
SELECT DISTINCT * 
FROM devstatechange 
WHERE MAX("when") AND new_state = 4 
ORDER BY "when";

SELECT DISTINCT device_id, new_state, MAX("when") 
FROM devstatechange 
WHERE new_state = 4  
ORDER BY "when";

```

with obviously no success.

I'm thinking I might need to 'group' the entries together, but I don't know how to specify 'return last entry *only* if new\_state = 4' in SQL, or rather PostgreSQL.

Any tidbits or pokes in the right direction would be appreciated.