Using Variables for Asc and Desc in Order By

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 set Order By ASC/DESC from a Variable

ORDER BY
  case when @Direction = 'ASC' THEN field END ASC ,
  case when @Direction = 'DESC' THEN field END DESC

Order by ASC or DESC based on parameter?

You have the right idea but just need to change the way your CASE is working a little:

ORDER BY
  CASE WHEN @IsDescOrder = 1 THEN DateKey ELSE '' END DESC,
  CASE WHEN @IsDescOrder = 0 THEN DateKey ELSE '' END ASC

This contains two clauses to order by, an descending order clause followed by an ascending one.

When @IsDescOrder is 1 then the descending clause is used, otherwise the ascending. By supplying ELSE '', this ensures that the unwanted order (either ASC or DESC) is effectively ignored.

EDIT

As pointed out by OP, this caused an error if the order column was numeric. To solve this, replace the empty string ('') with 0:

ORDER BY
  CASE WHEN @IsDescOrder = 1 THEN DateKey ELSE 0 END DESC,
  CASE WHEN @IsDescOrder = 0 THEN DateKey ELSE 0 END ASC

This works in the same was as the solution with the empty string, but prevents the type conversion error when using numeric columns.

Sql Order By ... using `Case When` for different Ascending, Descending, and Custom Orders

There are quite a few problems, e.g. You can't alias a calculation field in an order by, and you'll need to escape your table name, fix the cae, and count the parenthesis.

Also, since it seems you just want to CASE on a single variable, you can move the @OrderBy out to the top of the CASE, like so:

SELECT * from [table]
ORDER BY 
    CASE @OrderBy
        WHEN  0
            THEN [table].id -- ASC
        WHEN 2
            THEN [table].id * -1 -- DESC
    ---I want to do something like below as I don't have discount column in table
        WHEN 4
            THEN (100-([table].price/[table].oldprice)*100)
    END

SqlFiddle Here

As an aside, if you need to dynamically change the ASC or DESC of a column, you can use a hack like this by multiplying by -1.

(Also note that ORDER BY CASE ... END ASC, CASE ... END ASC will set the first and then second orderings ... this doesn't seem to make sense given that @OrderBy can only have a single value)

Room DAO Order By ASC or DESC variable

Use CASE Expression for SQLite to achieve this in Room DAO,

@Query("SELECT * FROM Persons ORDER BY 
        CASE WHEN :isAsc = 1 THEN first_name END ASC, 
        CASE WHEN :isAsc = 0 THEN first_name END DESC")
List<Person> getPersonsAlphabetically(boolean isAsc);

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.

---

Related Topics