How to create a Grafana variable that uses only one element of a Grafana query variable containing a list

In order to improve the performance of my dashboard, I would like to find a way to create a Grafana variable by using another Grafana variable that contains a list of elements.

The source variable is room_ids, which is a Query variable that uses a PostgreSQL data source. This is the query that defines the source variable:

SELECT DISTINCT roomid 
FROM periodic_measurements 
WHERE apartmentid = $apartment 
AND roomid != 0 
ORDER BY roomid;

Now let’s say I would like to use the variable room_ids to create a second variable called room1 which takes the first element of the list contained in room_ids. The variable room1 should provide the same result of the following Query variable :

SELECT DISTINCT roomid 
FROM periodic_measurements 
WHERE apartmentid = $apartment 
AND roomid != 0 
ORDER BY roomid 
LIMIT 1 OFFSET 0;

I am struggling to find out how shall to define the variable room1. I have tried different combinations such as $room_ids[1] or $room_ids(1) with no result.

Shall room1 be a Grafana Query variable or a Grafana Custom variable?

Shall I change room_ids to provide an array rather than a list?

Any hints?

I am running Grafana version 9.5.1. on Linux Ubuntu 22.04.2.

The solution is to use a single query with GROUP BY and a Grafana transformation that transposes the table:

SELECT roomid AS "room id", SUM(mvalue) AS "energy consumption in the room" FROM periodic_measurements WHERE ($__timeFilter(mtimestamp) AND apartmentid = $apartment AND metric = 5) GROUP BY roomid;