Skip to content
Advertisement

How to determine the order of the result from my postgres query?

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:

enter image description here

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 times 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
6 People found this is helpful
Advertisement