Conditional Order by Depending on Column Values

Conditional ORDER BY depending on column values

I'd say the BEST way to do this is in a single query is a CASE statement...

SELECT TOP 1 FROM ... ORDER BY 
    (CASE WHEN column1 IS NULL THEN column2 ELSE column1 END) 

Dynamic sorting by column depending on value in column

To answer. I did finally find the solution. It was quite simple and whilst I read through other answers, I was confused as to why it did not work for me. Apparently the index of a column will not work when using case when.

In the end, I put up the whole query with unions into a subquery with the solution being:

SELECT * FROM(
  SELECT id, name, sortbycolumn FROM table
  WHERE id = :in_id
  UNION
  SELECT id, name, null sortbycolumn FROM table
  WHERE id = :in_id
)
ORDER BY
    case when sortbycolumn = 1 THEN id,
    case when sortbycolumn = 2 then name
    else id end

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

conditional sorting a column dependent on another column value SQL

I think you want conditional aggregation:

SELECT PRIMARY_CUST_SRCE_REF_ID,
       SUM(CASE WHEN CREDIT_DEBIT_CODE = 'C' THEN TXN_AMOUNT_BASE ELSE 0 END) AS Credit,
       SUM(CASE WHEN CREDIT_DEBIT_CODE = 'D' THEN TXN_AMOUNT_BASE ELSE 0 END) AS Debit
FROM IDP_INTERFACE.V_L3_HPT_TRANSACTIONS
WHERE CREDIT_DEBIT_CODE IN ('C', 'D')
GROUP BY PRIMARY_CUST_SRCE_REF_ID;

This gets you the sum of credits and debits for each customer ref. If you want the ratio then divide the values.

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;

Conditionally Order by Ascending or Descending on the Same Column

we can make them into groups and do sorting on the group in the first order by clause.

   SELECT * 
    from test
    order by (case when [Date] is null then 0 
             when [Date] >= getdate() then 1 
             when [Date] < getdate() then 2 
             end ) asc ,
             case when [Date] >= getdate() then [Date] end asc,
             case when [Date] < getdate() then [Date] end desc

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

---

Related Topics