How do I query the postgreSQL state table for the latest sensor change?

Configuration
1

Hi

I’m struggling to find ways of diagnosing my issue so posting on this forum…

I’d like to get the entity_id of the last sensor changed using the following yaml set in my sensors.yaml:

  - platform: sql
    db_url: postgresql://loginname:somepassword@postgreSQLServerIP/hass
    queries:
      - name: SQLTest
        query: "select 1.0 as mysd;"
        column: 'mysd'
      - name: SQLLastSensor
        query: "select entity_id,state,last_changed from states order by state_id desc"
        column: 'entity_id'
      - name: Gym Bathroom Humidity Standard Deviation
        query: "select TRUNC(stddev(sd.svalue),2) as mysd from (SELECT CAST(s.state AS NUMERIC) as svalue FROM states s WHERE s.entity_id = 'sensor.shower_humidity' AND s.state<>'unavailable' AND s.state<>'unknown' ORDER BY state_id DESC LIMIT 60) sd;"
        column: 'mysd'

I have confirmed the logonname, somepassword and postgreSQLServerIP via pgAdmin 4 and run the queries directly in the Query tool, all return a single line result.
select 1.0 as mysd;
returns mysd as numeric 1.0

select entity_id,state,last_changed from states order by state_id desc limit 1;
returns 3 columns

select TRUNC(stddev(sd.svalue),2) as mysd from (SELECT CAST(s.state AS NUMERIC) as svalue FROM states s WHERE s.entity_id = 'sensor.shower_humidity' AND s.state<>'unavailable' AND s.state<>'unknown' ORDER BY state_id DESC LIMIT 60) sd;
returns mysd as numeric 2.13

When I restart HA and inspect the values of sensor.sqltest I correctly get a State of 1.0.
When I check the State of the remaining SQL sensors the values returned are ‘unknown’

I looked at home-assistant.log and only the following sql entries were there:

2021-05-12 17:19:41 INFO (SyncWorker_3) [homeassistant.loader] Loaded sql from homeassistant.components.sql
2021-05-12 17:19:43 INFO (MainThread) [homeassistant.components.sensor] Setting up sensor.sql
2021-05-12 17:19:54 WARNING (MainThread) [homeassistant.components.sensor] Setup of sensor platform sql is taking over 10 seconds.

Is there anywhere else I can see what’s going on?

PS originally I wanted to limit the number of return rows to 1 so I had originally added limit 1; at the end of the queries, but this threw an error in the logs showing there was an additional LIMIT 1 added to the line.

Any thoughts of what else I should try?