Answers.Body: <p>I see if we try to modify the sort col with any sql defined functions, it gets returned with the Error Message. For Eg - <code>['sort' =&gt; 'CAST(value AS decimal) DESC']</code> returns</p>
  
  <blockquote>
    <p>Unknown field specified for sort. Cannot order by 'CAST(value AS decimal) DESC'.</p>
  </blockquote>
  
  <p>Maybe, there should be some modification in <a href="https://github.com/civicrm/civicrm-core/blob/master/CRM/Utils/Rule.php#L129-L146" rel="nofollow noreferrer"><code>CRM_Utils_Rule::mysqlOrderBy()</code></a> to either handle such cases.</p>
  
  <p>Or we can simply use <code>get</code> instead of <code>getsingle</code> and then order the returned result values using array functions.</p>
  
Answers.ContentLicense: CC BY-SA 3.0
Answers.CreationDate: 2017-01-20T06:13:24.863Z
Answers.LastActivityDate: 2017-01-20T11:14:03.010Z
Answers.LastEditDate: 2017-01-20T11:14:03.010Z
Answers.UserDisplayName: jitendra - fuzion
Body: <p>I'm trying to find the activity_type with the highest value, so I can ++ that value and insert a new activity type. I thought this would be easy.</p>
  
  <p>I cannot sort on the "value" column of the <code>option_value</code> table because it is a varchar column containing (in this case) numbers.  Take the following api call:</p>
  
  <pre><code>civicrm_api3('OptionValue', 'getsingle', [
    'option_group_id' =&gt; 'activity_type',
    'options' =&gt; ['sort' =&gt; 'value DESC', 'limit' =&gt; 1]
  ]);
  </code></pre>
  
  <p>This ought to return the activity type with the highest value. However, it returns "9" because I guess mysql thinks the string "9" > "62" (which is the actual max in my sandbox).</p>
  
  <p>Anyone have a good approach to suggest?</p>
  
Tags: <api>
Title: How to sort a varchar numerically using the api
ThreadText: # How to sort a varchar numerically using the api
  
  (Asked by: username_0 on 2017-01-19)
  
  I'm trying to find the activity\_type with the highest value, so I can ++ that value and insert a new activity type. I thought this would be easy.
  
  I cannot sort on the "value" column of the `option_value` table because it is a varchar column containing (in this case) numbers. Take the following api call:
  
  ```
  civicrm_api3('OptionValue', 'getsingle', [
    'option_group_id' => 'activity_type',
    'options' => ['sort' => 'value DESC', 'limit' => 1]
  ]);
  
  ```
  
  This ought to return the activity type with the highest value. However, it returns "9" because I guess mysql thinks the string "9" > "62" (which is the actual max in my sandbox).
  
  Anyone have a good approach to suggest?
  
  **Tags:** api
  
  
  ---
  
  ## 1 answers
  
  ### Answer by username_1 on 2017-01-20. Score: 2
  
  I see if we try to modify the sort col with any sql defined functions, it gets returned with the Error Message. For Eg - `['sort' => 'CAST(value AS decimal) DESC']` returns
  
  > Unknown field specified for sort. Cannot order by 'CAST(value AS decimal) DESC'.
  
  Maybe, there should be some modification in [`CRM_Utils_Rule::mysqlOrderBy()`](https://github.com/civicrm/civicrm-core/blob/master/CRM/Utils/Rule.php#L129-L146) to either handle such cases.
  
  Or we can simply use `get` instead of `getsingle` and then order the returned result values using array functions.
  
  
  ---
  
  