T-SQL Conditional Order By
CASE
is an expression that returns a value. It is not for control-of-flow, like IF
. And you can't use IF
within a query.
Unfortunately, there are some limitations with CASE
expressions that make it cumbersome to do what you want. For example, all of the branches in a CASE
expression must return the same type, or be implicitly convertible to the same type. I wouldn't try that with strings and dates. You also can't use CASE
to specify sort direction.
SELECT column_list_please
FROM dbo.Product -- dbo prefix please
ORDER BY
CASE WHEN @sortDir = 'asc' AND @sortOrder = 'name' THEN name END,
CASE WHEN @sortDir = 'asc' AND @sortOrder = 'created_date' THEN created_date END,
CASE WHEN @sortDir = 'desc' AND @sortOrder = 'name' THEN name END DESC,
CASE WHEN @sortDir = 'desc' AND @sortOrder = 'created_date' THEN created_date END DESC;
An arguably easier solution (especially if this gets more complex) is to use dynamic SQL. To thwart SQL injection you can test the values:
IF @sortDir NOT IN ('asc', 'desc')
OR @sortOrder NOT IN ('name', 'created_date')
BEGIN
RAISERROR('Invalid params', 11, 1);
RETURN;
END
DECLARE @sql NVARCHAR(MAX) = N'SELECT column_list_please
FROM dbo.Product ORDER BY ' + @sortOrder + ' ' + @sortDir;
EXEC sp_executesql @sql;
Another plus for dynamic SQL, in spite of all the fear-mongering that is spread about it: you can get the best plan for each sort variation, instead of one single plan that will optimize to whatever sort variation you happened to use first. It also performed best universally in a recent performance comparison I ran:
http://sqlperformance.com/conditional-order-by
Conditional Order By in sql server with multiple order columns
I think this is what you want:
ORDER BY
CASE WHEN price IS NULL THEN name END DESC,
CASE WHEN price IS NOT NULL THEN price END DESC,
CASE WHEN price IS NULL THEN age END DESC,
CASE WHEN price IS NOT NULL THEN name END DESC;
Appreciate that when price
is NULL
, the above reduces to:
ORDER BY
name DESC,
NULL DESC,
age DESC,
NULL DESC;
That is, the alternate CASE
expressions just collapse to NULL
, leaving the above equivalent to:
ORDER BY
name DESC,
age DESC;
SQL Query with conditional order by for a specific condition
When I face this kind of situation, I generally do this:
SELECT [CompanyName]
, [CompanyCode]
, SortOrder = case when CompanyName is null then 3
when CompanyName = 'MyCompany' then 1
else 2
end
FROM [dbo].COND_ORDERBY_TEST
ORDER BY 3 asc, CompanyName asc
As you are always ordering by the same field, SQL Server will not guess what order do you want...
Try it :)
SQL Server Conditional Order By
Per the previous answer, try:
order by
case @SortBy
when 'product' then p.Description
when 'vendor' then v.LongName
else convert(VARCHAR(25),a.DateAdded,20)
This should give you the sort you want, as it will format the date string yyyy-mm-dd hh:mm:ss.
SQL Server conditional Order By with multiple columns
You are making this much more complex than it needs to be. Here is your SQL for ordering by start_date:
with inputdata as ( select 1 as id, 'OK' as state, convert(datetime, '2018-01-10 12:00') as start_date
union all select 2 as id, 'RUNNING' as state, convert(datetime, '2018-01-10 12:10') as start_date
union all select 3 as id, 'NOTOK' as state, convert(datetime, '2018-01-10 12:30') as start_date
union all select 2 as id, 'RUNNING' as state, convert(datetime, '2018-01-10 12:45') as start_date
union all select 2 as id, 'OK' as state, convert(datetime, '2018-01-10 13:00') as start_date
)
select * from inputdata order by case when state='RUNNING' then 1 else 2 end, start_date
This results in:
id state start_date
2 RUNNING 2018-01-10 12:10:00.000
2 RUNNING 2018-01-10 12:45:00.000
1 OK 2018-01-10 12:00:00.000
3 NOTOK 2018-01-10 12:30:00.000
2 OK 2018-01-10 13:00:00.000
SQL conditional order by?
You can use two keys in the order by
:
order by (t.completed = '0000-00-00 00:00:00') desc, -- put these first
t.completed desc
MS Sql: Conditional ORDER BY ASC/DESC Question
Don't change the ASC
or DESC
, change the sign of the thing being sorted-by:
SELECT * FROM table
ORDER BY
CASE WHEN @Direction = 1 THEN -id else id END asc;
The OP asks:
Guys, I am not the SQL Expert, please explain me what means the id and -id, does it controls the ordering direction?
id is just whatever column you're sorting by; -id is just the negation of that, id * -1. If you're sorting by more than one column, you'll need to negate each column:
SELECT * FROM table
ORDER BY
CASE WHEN @Direction = 1 THEN -id else id END
CASE WHEN @Direction = 1 THEN -othercolumn else othercolumn END ;
If you're ordering by a non numeric column, you'll need to find an expression that makes that column "negative"; writing a function to do that may help.
T-SQL Conditional ORDER BY A, B if A is not NULL ELSE B
try:
ORDER BY COALESCE(A,'')+COALESCE(B,'')
SQL Conditional Order By
ORDER BY COALESCE(PremiumUsers.IsActive, 0) DESC
That will group the NULLs with not-actives.
Related Topics
How to Speed Up Counting Rows in a Postgresql Table
Select Without a from Clause in Oracle
Is There a Postgres Closest Operator
Check If Null Exists in Postgres Array
For JSON Path Returns Less Number of Rows on Azure SQL
With Check Add Constraint Followed by Check Constraint VS. Add Constraint
What Is the Meaning of Select ... for Xml Path(' '),1,1)
Slow Bulk Insert for Table with Many Indexes
When Would You Use a Table-Valued Function
Query for Count of Distinct Values in a Rolling Date Range
SQL 2005 - the Column Was Specified Multiple Times
How to Improve Performance for Datetime Filtering in SQL Server
T-SQL Calculate Moving Average
Export Table Data from One SQL Server to Another