Sql Dynamic Asc and Desc

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

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.

Oracle dynamic DESC and ASC in order by

If you also want to make the sort order (ASC/DESC) dynamic, you could do the following:

SELECT ...
Order By CASE WHEN InputParam = 'PRICE' THEN l_so * OFFER_PRICE END,
         CASE WHEN InputParam = 'ENDING SOON' 
              THEN l_so * (SYSDATE - EXPIRY_DATE) END, 
         CASE WHEN InputParam = 'DISCOUNT' THEN l_so * DISC_PERCENTAGE END,
         CASE WHEN InputParam = 'SAVING' THEN l_so * SAVING END

with a variable l_so that contains 1 or -1 depending upon which sort order you want.

Dynamic ORDER BY and ASC / DESC in a plpgsql function

I would do it like this:

CREATE OR REPLACE FUNCTION list(
      _category varchar(100)
    , _limit int
    , _offset int
    , _order_by varchar(100)
    , _order_asc_desc text = 'ASC')  -- last param with default value
  RETURNS TABLE(id int, name varchar, clientname varchar, totalcount bigint)
  LANGUAGE plpgsql AS
$func$
DECLARE
   _empty text := '';
BEGIN
   -- Assert valid _order_asc_desc
   IF upper(_order_asc_desc) IN ('ASC', 'DESC', 'ASCENDING', 'DESCENDING') THEN
      -- proceed
   ELSE
      RAISE EXCEPTION 'Unexpected value for parameter _order_asc_desc.
                       Allowed: ASC, DESC, ASCENDING, DESCENDING. Default: ASC';
   END IF;
   
   RETURN QUERY EXECUTE format(
     'SELECT id, name, clientname, count(*) OVER() AS full_count
      FROM   design_list
      WHERE ($1 = $2 OR category ILIKE $1) 
      ORDER  BY %I %s
      LIMIT  %s
      OFFSET %s'
    , _order_by, _order_asc_desc, _limit, _offset)
   USING _category, _empty;
END
$func$;

Core feature: use format() to safely and elegantly concatenate your query string. Related:

• INSERT with dynamic table name in trigger function
• Format specifier for integer variables in format() for EXECUTE?

ASC / DESC (or ASCENDING / DESCENDING) are fixed key words. I added a manual check (IF ...) and later concatenate with a simple %s. That's one way to assert legal input. For convenience, I added an error message for unexpected input and a parameter default, so the function defaults to ASC if the last parameter is omitted in the call. Related:

• Optional argument in PL/pgSQL function
• ERROR: input parameters after one with a default value must also have defaults in Postgres

Addressing Pavel's valid comment, I concatenate _limit and _offset directly, so the query is already planned with those parameters.

_limit and _offset are integer parameters, so we can use plain %s without the danger of SQL injection. You might want to assert reasonable values (exclude negative values and values too high) before concatenating ...

Other notes:

• Use a consistent naming convention. I prefixed all parameters and variables with an underscore _, not just some.

• Not using table qualification inside EXECUTE, since there is only a single table involved and the EXECUTE has its separate scope.

• I renamed some parameters to clarify. _order_by instead of _sort_by; _order_asc_desc instead of _order.

Using variables for asc and desc in order by

without Dynamic SQL each option it's clause for example:

ORDER BY
  case when @var1 = 'priority asc' THEN priority END ASC ,
  case when @var1 = 'priority desc' then priority end DESC,
  case when @var2 = 'report_date asc' then report_date end ASC,
  case when @var2 = 'report_date desc' then report_date 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.

How to make order by clause dynamic

You need to combine the two. I would suggest this rather clunky code:

ORDER BY (CASE WHEN @iColumnName = 'ReceiptLocation' AND @iSortOrder = 'DESC'
               THEN ReceiptLocation
          END) DESC,
         (CASE WHEN @iColumnName = 'ReceiptLocation' 
               THEN ReceiptLocation
          END) ASC,
         (CASE WHEN @iColumnName = 'DeliverLocation' AND @iSortOrder = 'DESC'
               THEN DeliverLocation
          END) DESC,
         (CASE WHEN @iColumnName = 'DeliverLocation' 
               THEN DeliverLocation
          END) ASC,
         (CASE WHEN @iSortOrder = 'DESC'
               THEN NominationNbr
          END) DESC,
         NominationNbr ASC

Each CASE statement is a separate order key. However, the values are NULL if they do not match, so the key doesn't do anything with no match.

You can also implement this using dynamic SQL. That can be more effective if you have a simple query and indexes that can be used for the ORDER BY.

SQL Server dynamic sorting on multiple columns

ROW_NUMBER() OVER
            (
            ORDER BY
            --  string order by
            CASE @SortDirection
                  WHEN 'ASC'  THEN
                    CASE @SortIndex
                      WHEN 1 THEN SKU
                      WHEN 2 THEN BrandName + ',' + ItemName 
                      WHEN 3 THEN ItemName
                    END            
                END ASC,
                CASE @SortDirection
                  WHEN 'DESC' THEN
                    CASE @SortIndex
                      WHEN 1 THEN SKU
                      WHEN 2 THEN BrandName + ',' + ItemName
                      WHEN 3 THEN ItemName
                    END
                END DESC,

Use Brandname + ItemName in the When 2 Clause and to have both fields be used in the sort.

---

Related Topics