Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Using variables for asc and desc in order by [duplicate]

I understand that I can use variables in the order by section of sql queries like this:

order by  case when @var1 = 'priority' then priority end desc, case when @var2 = 'report_date' then report_date end asc 

But how do I use variables for the asc and desc sections too?

like image 261
oshirowanen Avatar asked Aug 13 '13 11:08

oshirowanen


People also ask

Can I use ORDER BY ASC and DESC in same query?

To sort in ascending or descending order we can use the keywords ASC or DESC respectively. To sort according to multiple columns, separate the names of columns by the (,) operator. Now consider the above database table and find the results of different queries.

How do I sort by both ASC and DESC in SQL?

The ORDER BY keyword is used to sort the result-set in ascending or descending order. The ORDER BY keyword sorts the records in ascending order by default. To sort the records in descending order, use the DESC keyword.

Can we use ORDER BY for 2 columns?

After the ORDER BY keyword, add the name of the column by which you'd like to sort records first (in our example, salary). Then, after a comma, add the second column (in our example, last_name ). You can modify the sorting order (ascending or descending) separately for each column.


2 Answers

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 
like image 59
Luis LL Avatar answered Sep 19 '22 05:09

Luis LL


Assuming your variable @var3 stores 'ASC' or 'DESC' keywords, you can write something like this:

order by  case when @var1 = 'priority' and @var3 = 'DESC' then priority end DESC, case when @var1 = 'priority' and @var3 = 'ASC' then priority  end ASC, case when @var2 = 'report_date' and @var3 = 'ASC' then report_date end ASC, case when @var2 = 'report_date' and @var3 = 'DESC' then report_date end DESC 
like image 43
Nenad Zivkovic Avatar answered Sep 22 '22 05:09

Nenad Zivkovic