Dynamic Order Direction

Dynamic order direction

You could have two near-identical ORDER BY items, one ASC and one DESC, and extend your CASE statement to make one or other of them always equal a single value:

ORDER BY
      CASE WHEN @OrderDirection = 0 THEN 1
      ELSE
           CASE WHEN @OrderByColumn = 'AddedDate' THEN CONVERT(varchar(50), AddedDate)
                WHEN @OrderByColumn = 'Visible' THEN CONVERT(varchar(2), Visible)
                WHEN @OrderByColumn = 'AddedBy' THEN AddedBy
                WHEN @OrderByColumn = 'Title' THEN Title
           END
      END ASC,
      CASE WHEN @OrderDirection = 1 THEN 1
      ELSE
           CASE WHEN @OrderByColumn = 'AddedDate' THEN CONVERT(varchar(50), AddedDate)
                WHEN @OrderByColumn = 'Visible' THEN CONVERT(varchar(2), Visible)
                WHEN @OrderByColumn = 'AddedBy' THEN AddedBy           
                WHEN @OrderByColumn = 'Title' THEN Title
           END
      END DESC

dynamic order by sql postgresql

Use two different order by keys:

ORDER BY (case when p_sort_direction = 'ASC' then p_sort_column end)
 asc,
            p_sort_column desc
LIMIT p_total OFFSET (p_page * p_total);

Note that you have another problem. p_sort_column is a string. You would need to use dynamic SQL to insert it into the code.

Alternatively, you can use a series of cases:

order by (case when p_sort_column = 'column1' and p_sort_direction = 'ASC' then column1 end) asc,
         (case when p_sort_column = 'column1' and p_sort_direction = 'DESC' then column1 end) desc,
         (case when p_sort_column = 'column2' and p_sort_direction = 'ASC' then column2 end) asc,
         (case when p_sort_column = 'column2' and p_sort_direction = 'DESC' then column2 end) desc,
         . . .

How to specify dynamic order by clause in Oracle

Order by two outer CASE expressions, one ASC one DESC. In the first one check if you want to sort ASC and if not let the expression return a constant or NULL, i.e. it doesn't change the order. For the case you want to sort ASC add an inner CASE expression, that returns the column to sort by. Analogously handle the DESC case.

...
ORDER BY CASE
           WHEN in_order_dir = 'ASC' THEN
             CASE
               WHEN in_order_by = 1 THEN
                 city_code
               WHEN in_order_by = 2 THEN
                 city_name
               ...
             END
         END ASC,
         CASE
           WHEN in_order_dir = 'DESC' THEN
             CASE
               WHEN in_order_by = 1 THEN
                 city_code
               WHEN in_order_by = 2 THEN
                 city_name
               ...
             END
         END DESC;

Maybe you have to adapt it because of type incompatibilities between the columns, I can't tell that from what you posted. But it should convey the general concept.

---

Edit:

On the type problem:

One possibility is to convert the columns to compatible data types in a way the order is kept. For instance, if you have a char_column (of the type char), a date_column and an integer_column (of date and integer) you could do

to_char(date_column, 'YYYYMMDDHH24MISS')

to convert the date and

lpad(11, 38, '0')

to convert the integer to char.

The cleaner thing to do, but the one with more work to do (for the programmer, in terms of performance there should be no significant difference whatsoever) is to split the expressions once more. I.e. have an outer CASE for each type your columns are for each direction.

Like

ORDER BY CASE
           WHEN in_order_dir = 'ASC' THEN
             CASE
               WHEN in_order_by = 1 THEN
                 char_column1
               WHEN in_order_by = 2 THEN
                 char_column2
               ...
             END
         END ASC,
         CASE
           WHEN in_order_dir = 'ASC' THEN
             CASE
               WHEN in_order_by = 3 THEN
                 date_column1
               WHEN in_order_by = 4 THEN
                 date_column2
               ...
             END
         END ASC,

and so on for all types and analogously for DESC. Remember, if the column isn't meant to be sorted after such CASE will yield NULL and sorting by that won't influence the order.

Dynamic direction of ORDER in ActiveRecord query when using a JOIN

Ordering by a joined association is only supported via strings I fear.

However, it is possible to merge scopes which allows you to write:

->(direction) { Account.joins(:users).merge(User.order(id: direction})).first }

This does prevent values other than [:asc, :desc, :ASC, :DESC, "asc", "desc", "ASC", "DESC"] for direction (plucked from the ArgumentError message). In my opinion it comes at the cost of decreased readability but one can try to mitigate the problem and increase reusability at the same time by defining the order as a named scope in the User class.

class User
   ...
   self.ordered_by_id(direction)
     order(id: direction})
   end
   ...
end

Which enables you to use

->(direction) { 
  Account.joins(:users).merge(User.ordered_by_id(direction)).first
} 

Granted, in the current example this does not shine yet, but using merge with more complicated scopes can greatly reduce duplication.

Dynamic Order By Clause

I would implement the dynamic sorting differently. I believe by doing this the database engine will be able to use indexes more effectively (provided you've got them)

 SELECT *
    FROM @employee
    ORDER BY 
    CASE WHEN @direction = 'DESC' AND @searchCol = 'NAME' THEN Name ELSE ' ' END DESC,
    CASE WHEN @direction = 'DESC' AND @searchCol = 'GENDER' THEN Gender ELSE ' ' END DESC,
    CASE WHEN @direction = 'DESC' AND @searchCol = 'DOB' THEN DOB ELSE CAST(0 AS DATE) END DESC,
    CASE WHEN @direction = 'DESC' AND @searchCol = 'PROJECTS' THEN TotProjects ELSE 0 END DESC,
    CASE WHEN @direction = 'ASC' AND @searchCol = 'NAME' THEN Name ELSE ' ' END,
    CASE WHEN @direction = 'ASC' AND @searchCol = 'GENDER' THEN Gender ELSE ' ' END,
    CASE WHEN @direction = 'ASC' AND @searchCol = 'DOB' THEN DOB ELSE CAST(0 AS DATE) END,
    CASE WHEN @direction = 'ASC' AND @searchCol = 'PROJECTS' THEN TotProjects ELSE 0 END

How do I write a Dynamic LINQ query and have Orderby [Sort Direction]

You could write your own SortBy extension method that takes a SortDirection.

public enum SortDirection { Ascending, Descending }

public static class Extensions
{
    public static IEnumerable<TSource> SortBy<TSource, TKey>(
        this IEnumerable<TSource> source,
        SortDirection sortDirection,
        Func<TSource, TKey> keySelector)
    {
        switch (sortDirection)
        {
            case SortDirection.Ascending:
                return source.OrderBy(keySelector);
            case SortDirection.Descending:
                return source.OrderByDescending(keySelector);
            default:
                throw new ArgumentOutOfRangeException();
        }
}

Usage

var sortDirection = Sort.Descending; // this could be set dynamically at runtime
var sorted = new[] { 1, 2, 3 }.SortBy(SortDirection.Descending, x => x);

SQL Dynamic ASC and DESC

You can do solutions like @TonyAndrews by manipulating numeric or data values. For VARCHAR2 an alternative to dynamic SQL could be to have two expressions:

order by
   case when :sorting='ASC' then col1 end ASC,
   case when :sorting='DESC' then col1 end DESC

When :sorting has the value 'ASC' the result of that ORDER BY becomes like if it had been:

order by
   col1 ASC,
   NULL DESC

When :sorting has the value 'DESC' the result of that ORDER BY becomes like if it had been:

order by
   NULL ASC,
   col1 DESC

One downside to this method is that those cases where the optimizer can skip a SORT operation because there is an index involved that makes the data already sorted like desired, that will not happen when using the CASE method like this. This will mandate a sorting operation no matter what.

---

Related Topics