SQL Server Conditional Order By

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.

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 :)

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

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.

Conditional ordering in SQL Server

SELECT *
FROM (VALUES (998), (999), (1000), (1001), (1002), (1003), (1004)) AS T(ID)
ORDER BY CASE
        WHEN T.ID BETWEEN 1000 AND 1004 THEN 1
        ELSE 2
    END
    , ID DESC;

Took sample data from Sick's answer.

How does this work?

1. It checks whether your ID is matching your criteria and assign it a
   value of 1
2. Everything else will have 2
3. It will sort by this value first
4. Your IDs with matching criteria will ALWAYS come first
5. Then we sort leftovers by ID in DESC order

Output:

╔══════╗
║  ID  ║
╠══════╣
║ 1004 ║
║ 1003 ║
║ 1002 ║
║ 1001 ║
║ 1000 ║
║  999 ║
║  998 ║
╚══════╝

I've made additional example, which generates 676.800 rows in our DB:

;WITH TestTable (ID)
AS (
    SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
    FROM sys.objects
    CROSS JOIN sys.schemas
    )
SELECT *
FROM TestTable AS T
ORDER BY CASE
        WHEN T.ID BETWEEN 1000 AND 1004 THEN 1
        ELSE 2
    END
    , ID DESC;

That's its result:

╔════════╗
║  ID    ║
╠════════╣
║ 1004   ║
║ 1003   ║
║ 1002   ║
║ 1001   ║
║ 1000   ║
║ 676800 ║
║ 676799 ║
║ 676798 ║
║ 676797 ║
║ 676796 ║
║ 676795 ║
║ ...    ║
║ 1006   ║
║ 1005   ║
║ 999    ║
║ 998    ║
║ ...    ║
║ 1      ║
╚════════╝

SQL Conditional Order By

ORDER BY COALESCE(PremiumUsers.IsActive, 0) DESC

That will group the NULLs with not-actives.

---

Related Topics