Custom Sort options

Question

It would be very useful to be able to sort tables (grid and lookups) by something other than just columns ascending or descending.

For example to sort a column which will render NULLS at the top, but then the NON-NULL values following in descending order you'd need to specify something like:

ORDER BY CASE WHEN Col1 IS NULL THEN 1 ELSE 2 END asc, col1 desc.

You could, in dbFront perhaps, for total flexibility, allow the user to define the sort order as:

1. By column asc or desc [as current]
2. A column expression [example above] for any or all of the columns or,
3. Just provide the entire ORDER BY clause

Answer 1

Something like what you requested can already be accomplished using a view or calculated field. You could create a column who's sole purpose is to sequence the data.

You could call the view or calculated column something like "DefaultSort". This column could contain expressions like CASE WHEN Col1 IS NULL THEN 1 ELSE 2 END.

You can then select that column as All or Part of that tables sort order.

NOTE: A second answer was added which only deals with modifying the way that NULL values sort. Please vote on that answer if it would solve your problem.

If the provided solution is not satisfactory then please let me know in the comments and vote on this feature.

Comments

This is what I meant by my recent feature request, and I would also be keen to see this implemented.
1 thing I have noticed that relates to this is dbFront doesn't allow Views to act as Tables in the UI. I think that would be really beneficial, as it would allow for sorting like this, and also provide an extra layer of abstraction between the DB and user.

---

Thanks Anthony. I should taken the time to work this out for myself - your solution certainly does what I'd want to do.
Regards
Colin

---

@ConnorK, Allowing views to act as Tables would be another feature request.

Answer 2

This answer only deals with adding the ability to sort nulls differently then the main sort order.

Currently each field can be sorted Ascending or Descending.

The option would be to expand the sort options to include:

• Ascending (Nulls First)
• Ascending (Nulls Last)
• Descending (Nulls Last)
• Descending (Nulls First)

NOTE: Please vote on this Feature Request and this specific answer if it interests you.

Notes to self

• MySQL: ORDER BY columnName IS NULL DESC, columnName DESC
• Oracle: ORDER BY "columnName" ASC nulls LAST
• SQL Server: ORDER BY ISNULL([columnName], %MaxValue%)