Order by two columns with usage of CASE WHEN

Question

I have a query like this:

SELECT 
    ROW_NUMBER() OVER(ORDER BY USER_FNM, USER_LNM) AS ROW_NUM,
    USER_TEL, USER_FAX, USER_MOB            
FROM 
    BAUSER      
ORDER BY            
    CASE 
       WHEN @cOrderBy = 'USER_FNM_USER_LNM ASC' THEN USER_LNM 
    END ASC,
    CASE 
       WHEN @cOrderBy = 'USER_FNM_USER_LNM DESC' THEN USER_LNM 
    END DESC,
    CASE 
       WHEN @cOrderBy IS NULL THEN USER_KEY 
    END ASC
    OFFSET @iStartIndex ROWS
    FETCH NEXT @iRowsPerPage ROWS ONLY

What I would like to do is sorting by two columns - but this is showing syntax error:

CASE WHEN @cOrderBy = 'USER_FNM_USER_LNM ASC' THEN USER_LNM, USER_FNM END ASC

Any idea how to sort by two columns in this case?

Possible duplicate of [T-SQL Conditional Order By](https://stackoverflow.com/questions/15621609/t-sql-conditional-order-by) — SqlKindaGuy, Nov 20 '17 at 09:35
This has nothing to do with my question. The guy is asking how to do sorting - I already know that, which you can see from my question. I don't know how to order by two columns. — FrenkyB, Nov 20 '17 at 10:31
@FrenkyB, you should look at the linked article in the accepted answer. — Alex, Nov 20 '17 at 11:29

Alex · Accepted Answer · 2017-11-20T11:31:38.597

3

CASE is an expression that returns a single expression/value. You need to write one CASE statement per column:

ORDER BY            
CASE WHEN @cOrderBy = 'USER_FNM_USER_LNM ASC'        THEN USER_LNM END ASC,
CASE WHEN @cOrderBy = 'USER_FNM_USER_LNM DESC'       THEN USER_LNM END DESC,
CASE WHEN @cOrderBy IS NULL                          THEN USER_KEY END ASC,

CASE WHEN @cOrderBy = 'USER_FNM_USER_LNM ASC'        THEN USER_FNM END ASC,
CASE WHEN @cOrderBy = 'USER_FNM_USER_LNM DESC'       THEN USER_FNM END DESC

Update (to reflect updated question)

Since you have ROW_NUM column, you can sort by that:

CASE WHEN @cOrderBy = 'USER_FNM_USER_LNM ASC'        THEN ROW_NUM END ASC,
CASE WHEN @cOrderBy = 'USER_FNM_USER_LNM DESC'       THEN ROW_NUM END DESC,
CASE WHEN @cOrderBy IS NULL

edited Nov 20 '17 at 11:31

answered Nov 20 '17 at 09:06

Alex

4,885
3
19
39

Why have you deleted the part that works? USER_LNM + USER_FNM – FrenkyB Nov 20 '17 at 10:29
@FrenkyB strictly speaking sorting would not work accurately with USER_LNM + USER_FNM unless they are both fixed length CHAR types. So poor performance is not the primary concern. – Alex Nov 20 '17 at 11:27

Order by two columns with usage of CASE WHEN

1 Answers1

Linked