Order By ascending if negative, descending if positive

[Vorbeck]

Hi,

I am wondering if there is a way to incorporate an IIF statement into a Order By statement. I am trying to set up a query so that when X>0, field Y sorts descending but when X<0 field Y sort ascending.

For context, I am trying to show the top 5 absolute values, and the top drivers of those positions. So for example if I have a large negative result, what are the largest negative drivers of that result and vice versa.

Any help would be appreciated. Thanks.

 

[plog]

Where is X? Is that a field within or in the data source underlying the query? Could you post some sample data?

 

[MSAccessRookie]

Hi,

I am wondering if there is a way to incorporate an IIF statement into a Order By statement. I am trying to set up a query so that when X>0, field Y sorts descending but when X<0 field Y sort ascending.

For context, I am trying to show the top 5 absolute values, and the top drivers of those positions. So for example if I have a large negative result, what are the largest negative drivers of that result and vice versa.

Any help would be appreciated. Thanks.

If I understand you correctly, you want the positive numbers to be descending (5, 4, 3, 2, 1) and the negative numbers to be ascending (-5, -4, -3, -2, -1) at the same time.

If this is the case, then the problem might not be as complicated as you think it is. I believe that the Abs() function can be used to obtain the results that you want. If you say ORDER BY Abs(YourValue), then ascending and descending will appear to be reversed on opposite sides of zero. See if it does what you want.

-- Rookie

 

[Vorbeck]

Where is X? Is that a field within or in the data source underlying the query? Could you post some sample data?

X would be from another query. I used a query to rank the results on absolute terms, then I want to, one by one, display the top drivers for the top 3 ranked results (i.e. in 3 different queries). This issue is, using the Top Values depends on the sort order, so I need the sort to be ascending for negative values and descending for positive values.

As an example:

Table 1
CUR X Rank
-----------
CAD -2 1
CAD -8 1
CAD 1 1
CAD -3 1
USD 8 2
USD -1 2
EUR 3 3
EUR -7 3

Table 3
CUR Tot Rank
-----------
CAD -12 1
USD 7 2
EUR -4 3

In 3 queries, I would want CAD and EUR to sort Ascending, USD to sort Descending - because I only want the top result (positive or negative).

 

[nanscombe]

If ORDER BY Abs(YourValue) doesn't quite work how about trying ..

ORDER BY Sgn(YourValue), Abs(YourValue)

Sgn would sort by negative / Zero /Positive initially.

ABS would then sort by the Absolute value, i.e. with no Positive or negative.

 

[vbaInet]

Where is X? Is that a field within or in the data source underlying the query? Could you post some sample data?

@Vorbeck: So is X not criteria?

Based on these records:


Table 1
CUR X Rank
-----------
CAD -2 1
CAD -8 1
CAD 1 1
CAD -3 1
USD 8 2
USD -1 2
EUR 3 3
EUR -7 3

Show us what you expect it to be when sorted.