Sorting/Ordering

  • Thread starter Thread starter Rick
  • Start date Start date
R

Rick

The sql below is a real simple query that returns loan
count by status.

SELECT UDM_V_PMT_CURR_MO.STATUS, Count
(UDM_V_PMT_CURR_MO.LOAN_NUM) AS CountOfLOAN_NUM
FROM UDM_V_PMT_CURR_MO
WHERE (((UDM_V_PMT_CURR_MO.INV_CODE) In ("223")) AND
((UDM_V_PMT_CURR_MO.STATUS) Not In ("CLOSED")))
GROUP BY UDM_V_PMT_CURR_MO.STATUS
ORDER BY UDM_V_PMT_CURR_MO.STATUS;

What I need is to be able to sort/order the statuses in
any order I choose. For example, when sorting ascending
the order is as follows:

30 DPD
60 DPD
90 DPD
CURRENT
FC
REM
REO
REPERF

I would like to be able to change the order based on an
investor's request. For example, in the list above change
the order so that CURRENT appears first then the rest of
the statuses follow as is.

Thanks for your help!

Rick
 
Rick,
SQL only provides 2 sort orders, ASC and DESC. With character fields you
will never get "CURRENT" to be anywhere but in the middle of the list that
you provided as an example. One possible option would be to add a special
sort order field that is numeric and you put number values in it
corresponding to the sort order you want. the following is merely an
example.

SortOrder Status
2 30 DPD
3 60 DPD
4 90 DPD
1 CURRENT
5 FC

........ Other records below that.
 
That is exactly what I did. I actually created a table
and listed the statuses with a sort order. I was asking
in case there was a better way of accomplishing this task.

Thanks again for the help.

Rick
 
I agree that is probably the best solution. But just to be argumentative.

You could
* write a custom function to produce the sort order using
a case statement
* use a complex nested IIF function
* Use the Switch function

So there are other solutions, but the normal best solution is to use a table to
set up a special sort order.
 
Ok...I'll confess that there ARE other ways. However, the table approach is
so much better from a setup and maintenance perspective as to be, virtually,
the only way to go. Now if he were to get into the problem of needing
multiple special sort orders, then moving to a custom function or one of the
other approaches might be needed.
 
Actually if the OP needed multiple sort orders I would just add another field to
the table to identify the sort order group.

And I guess it is time to close this thread.
 
Back
Top