I have the following query:
SELECT time as "time", case when tag = 'KEB1.DB_BP.01.STATUS.SOC' THEN 'SOC' when tag = 'KEB1.DB_BP.01.STATUS.SOH' THEN 'SOH' end as "tag", value as "value" FROM metrics WHERE ("time" BETWEEN '2021-07-02T10:39:47.266Z' AND '2021-07-09T10:39:47.266Z') AND (container = '1234') AND (tag = 'KEB1.DB_BP.01.STATUS.SOC' OR tag = 'KEB1.DB_BP.01.STATUS.SOH') GROUP BY 1, 2, 3 ORDER BY time desc LIMIT 2
This is giving me the result:
Sometimes the order changes of the result changes from SOH -> SOC or from SOC -> SOH. I’m trying to modify my query so I always get SOH first and than SOC.. How can I achieve this?
Advertisement
Answer
You have two time
s that are identical. The order by
is only using time
as a key. When the key values are identical, the resulting order for those keys is arbitrary and indeterminate. In can change from one execution to the next.
To prevent this, add an additional column to the order by
so each row is unique. In this case that would seem to be tag
:
order by "time", tag