Sorting Problem

[abdullah123456789]

Hi,

I displayed the 'Historic List' in Geo SCADA Expert 2021. But, when i listed the datas with sorting by value, sorting is step by step, not the whole number as in the screenshot. I need to sort the datas 'descend' like 9898-9821-9730-9723-987-98. Where can i find this sorting settings?

 

[Experimentalist]

Hi

It looks like your query is using formatted value which is a string. Include the value column which is a number and sort by that. Use the schema to find the column you need

Regards

Ex

[sbeadle]

You may wish to display the text column (with units) but sort by the numeric value.

Sorted columns must appear in the SELECT column list, so add an underscore to the name alias to hide a column.

e.g:

SELECT TOP( 1000000 )
"RecordId", "RecordTime" AS "~Time", "FormattedValue", "ValueAsReal" AS "_ValueAsReal", "StateDesc", "QualityDesc", "ReasonDesc", "StatusDesc", "SuppressionTypeDesc", "Foreground", "Background", "Blink", "FileOffset" AS "_FileOffset"
FROM
CDBHISTORIC
WHERE
"Id" = 423621 AND "~Time" BETWEEN { OPC 'Hour - 23 Hours' } AND { OPC 'Hour - 23 Hours+1 Day' }
ORDER BY
"_ValueAsReal" ASC

[MF_SHAIKH]

Hello,

You can try below query

select * from table_name order by abs(column_name) desc;

Hope it helps..

Thanks

[BevanWeiss]

Why would you include the Absolute function (abs)??

SELECT column_name

FROM table_name

ORDER BY column_name DESC

Would do the ordering.

Including ABS(xx) is superfluous and significantly hurts performance.

It requires an additional mathematical calculation (to take the absolute of the column value), and reduces the ability of the query processor from being able to use some additional ordering optimisations.

As Steve mentioned however, this would result in the column no longer having nice unit formatting (i.e. m/s etc) since to add the unit requires the column be a string type, which then results in lexigraphic sorting (i.e. by character value, not numeric value).

Having a hidden column of the desired sort is the best work around.  However if the sorting needs to be dynamic (i.e. controlled by a user) then this isn't as usable.

[abdullah123456789]

Thank you for your help but it didn't solve my problem literally. Because it adds a new column and sort datas according to this column and hide the column. The problem starts here, when i change the sort column i am not sorting according to this column again because it is hidden.

[sbeadle]

So I guess if you want users to choose which column to sort, then you'll need to use the numeric value without units:

SELECT TOP( 1000000 )
"RecordId", "RecordTime" AS "~Time",  "ValueAsReal", "StateDesc", "QualityDesc", "ReasonDesc", "StatusDesc", "SuppressionTypeDesc", "Foreground", "Background", "Blink", "FileOffset" AS "_FileOffset"
FROM
CDBHISTORIC
WHERE
"Id" = 423621 AND "~Time" BETWEEN { OPC 'Hour - 23 Hours' } AND { OPC 'Hour - 23 Hours+1 Day' }
ORDER BY
"ValueAsReal" ASC

If you need units on display then you could bring that in from the point table using a join.