Link to home
Start Free TrialLog in
Avatar of ddunlea
ddunlea

asked on

Select statement wil complicated sort order.

Hi All,

I need to perform a select on a datatable with a rather complicated ordering. I've already used multiple, comma separated terms for multiple levels of ordering and the program hasn't complained, which is nice. Next of all however, I need to perform a sort based on the difference between two values. I have a column containing integers and I want to order by the closest match to another integer. I'm porting this from a MySQL app which uses:

"ORDER BY ABS(nominal - 10)"

10 is only for illustration, the value varies. I have not been able to find any equivalent to the ABS operator. The documentation mentions +, -, *, / and % as operators, and lists other characters that need to be escaped such as |, & and ^, but gives no indication as to why and what they can be used for if not escaped.

All help greatly appreciated. Thanks in advance.
Avatar of Lacutah
Lacutah
Flag of United States of America image

What database are you trying to port to?
Avatar of _TAD_
_TAD_



ABS is a valid select function.  Have you thought about adding that field in your select criteria and then sorting by that field?


Select ABS(Col1 - Col2) as ColAbs from MyTable Order by ColAbs
Avatar of ddunlea

ASKER

Hi Lacutah,

I'm not trying to port to a database at all. I'm building a static executable which is based on a snapshot of an existing database. The datatable is populated manually durinig the initialisation code without referencing any external database.
Avatar of ddunlea

ASKER

Hi _TAD_

I have put the following in my code:

DataRow[] matches  = table.Select("somecolumn = 1", "ABS(nominal - 10)");

And I get an exception saying "cannot find column ABS(nominal-10)"

It is valid SQL, but apparently not valid for a DataTable Select statement.
you might not be able to use all T-SQL statements in the datatable.select property. its a limited amount of commands.if you were to use this statement it needs to know what column to compare ABS(nominal-10) to.
Avatar of ddunlea

ASKER

That's a bit silly, isn't it? ABS(nominal-10) returns a numeric value, and it's trying to sort the rows. Surely it makes sense then that it should just sort  based on that rumeric value. Do I need to invent a dummy column purely for the sake of comparison?
ASKER CERTIFIED SOLUTION
Avatar of _TAD_
_TAD_

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of ddunlea

ASKER

Stupid indeed. Especially as the calculated column will vary on each and every query.