Embedded Query, add ability to sort by hidden column tied to visible column (i.e. for natural sorting of current value formatted)

[BevanWeiss]

We often use embedded query lists to present data, since they allow for a large amount of data to be presented, which easily accommodates the addition of new sites / information points to the list of data.

One issue we have however, is that we want the data presentation to appear 'pretty', that is that values should display alongside their units.  So if we have wet well levels, we would want the data column to appear as '43 %' or '946 mm'.  However, we also want it to be sorted naturally, and not lexigraphically (by character).

Currently if we have three values, '9 %', '10 %' and '91 %' if the user sorts by this column (ascending) it will display as

'10 %'

'9 %'

'91 %'

When what we want is:

'9 %'

'10 %'

'91 %'

As the current value formatted column is simply a string, then I don't think there is anything that the embedded query list should do for this.

BUT... what I think should be possible is to have a 'special column' similar to what exists for the foreground, background, blink columns. it could be sortorder or similar.  It would mean that when the following column is 'sorted' then the values in the 'sortorder' column preceeding it are actually used instead of the selected column values.

In this way if we had a query that looked like:

SELECT Name, CurrentValueAsReal as "{sortorder}", CurrentValueFormatted as "Value"

FROM TABLE

If the user clicked on the Value heading to sort, it would sort 'correctly' in accordance with the CurrentValueAsReal column, hence smallest at the top and largest at the bottom (or vice versa), and the world would be a better place 🙂

[AdamWoodland]

You can effectively do this currently by just aliasing a hidden column (prefix with _) and then specify that alias in the ORDER BY, the only issue would be if the user clicks a header for their own sort on the non-maths column and it all goes to pot.

SELECT
 NAME, CURRENTVALUEFORMATTED, CURRENTVALUEASREAL AS "_Sort"
FROM
 CDBPOINT
WHERE
 FULLNAME LIKE 'zTest.%'
ORDER BY
 "_Sort" ASC

[BevanWeiss]

Hi Adam,

It's exactly for the user-driven sorts that we would want this.

We've never got a problem having the table default sorted.

As you mention, just a hidden column and a static ORDER BY clause.

But the users often (read: always) want to slice/dice our embedded queries based on other things... like Estimated Time To Spill, or Estimated Infiltration Rate, or Estimated Inflow Rate, or Candelabras per Gazebo.. etc etc etc..  values need units, and hence all of our columns have units displayed for them.

[AdamWoodland]

Its almost as though to need to alias (bad use of wording but I'm going with it) the column as something else for the purpose of sorting, i.e.

SELECT Name, CurrentValueFormatted AS "CurrentValue" SORT CurrentValueAsReal FROM CDBPoint

No idea what the SQL spec allows for though, I just made that up 🙂

[BevanWeiss]

Nice idea, but I think it's in the display side that the issue arises.

Since it's the user performing the sort determination by clicking the header of the column.

I don't think anything in the 'pure' SQL side would resolve the issue.  I think it would need to be in the presentation side (ViewX).. but would need to have the information available from the SQL side (e.g. via the special column syntax that I mentioned.. which is kind of inline with the current foreground, background stuff).

My thought there is that ViewX itself wouldn't be doing the quicksort or similar itself, just when the user clicks on the heading column, instead of ViewX appending 'ORDER BY "CurrentValueFormatted" ASC' it would append 'ORDER BY "{sortorder1}" ASC', which would also handle nested sorts trivially, in the same way the current SQL Embedded Query List stuff does...

e.g. a query like

SELECT

  CurrentValueAsReal AS {sortorder1}, CurrentValueFormatted AS Value,

  HighHighLimitStd AS {sortorder2}, FORMATVALUE(HighHighLimitStd USING Format || ' ' || Unit ) AS HighHigh,

  HighLimitStd AS {sortorder3}, FORMATVALUE(HighLimitStd USING Format || ' ' || Unit ) AS High,

  LowLimitStd AS {sortorder4}, FORMATVALUE(LowLimitStd USING Format || ' ' || Unit ) AS Gazebos,

  LowLowLimitStd AS {sortorder5}, FORMATVALUE(LowLowLimitStd USING Format || ' ' || Unit ) AS Candelabras

FROM CDNP3AnalogIn

would be able to have nested sorting created by a user clicking on any of the 5 column headings, and it would sort it nicely using the provided sort columns which could end up like

SELECT

  CurrentValueAsReal AS {sortorder1}, CurrentValueFormatted AS Value,

  HighHighLimitStd AS {sortorder2}, FORMATVALUE(HighHighLimitStd USING Format || ' ' || Unit ) AS HighHigh,

  HighLimitStd AS {sortorder3}, FORMATVALUE(HighLimitStd USING Format || ' ' || Unit ) AS High,

  LowLimitStd AS {sortorder4}, FORMATVALUE(LowLimitStd USING Format || ' ' || Unit ) AS Gazebos,

  LowLowLimitStd AS {sortorder5}, FORMATVALUE(LowLowLimitStd USING Format || ' ' || Unit ) AS Candelabras

FROM CDNP3AnalogIn

ORDER BY

  {sortorder2} ASC, {sortorder1} DESC, {sortorder3} ASC, {sortorder4} ASC, {sortorder5} DESC

Not a super super nice query (since the sort columns lose some context).. but it would be pretty functional, and should be a pretty easy ViewX code change 😉

[sbeadle]

Hidden columns (preceded with underscore) can be sorted in the query SQL, but if you click on another column to sort, you can't then click on a hidden column to resort.

Thank you.

[du5tin]

Similar, but not the same... we have lists of wells on our systems and we format the column as as string with a limited number of significant figures to make the column width reduce (float columns are extremely wide!). When we do this the columns are sorted as strings. We have worked around it like Bevan has by padding the front of the column with spaces which actually makes the column appear to sort properly again. For example, 1.0 might have two spaces padding the front and 100.0 has no spaces padding the front. 

It would be great to get the column widths down for numbers too. But using format value on a number column should hopefully still sort like a number. Maybe that is something can get tweaked in the SQL interpreter?