How to order 2 SQL fields in asc and desc dynamically

Question

I want to order SQL Select Query where there are 2 fields that are in order. Then I need to decide if it is Descending and the other Ascending. How it's done

I need something like:

Select * from Customer
Order By Date @asc_or_Desc_date, Name @asc_or_Desc_name

Does anyone have any ideas?

I tried this, but it seems to fail

SELECT 

    Customer_ID,                        
    Name,                               
    Age                                         

FROM #Customer
ORDER BY 

    CASE WHEN @fieldSort ='Name'
        THEN ROW_NUMBER() over (order by Name) * 
            case when @directionOfSort = 'A' 
                THEN 1 ELSE -1 END,
             ROW_NUMBER() over (order by Age) * 
            case when @directionOfSort = 'A' 
                THEN 1 ELSE -1 END,
        END

Does anyone know how to sort this?

Answer 1

You will need to create the SQL statement dynamically in order to use the variable:

DECLARE @asc_desc VARCHAR(4);

SET @asc_desc = 'DESC';

DECLARE @sql NVARCHAR(1000);

SET @sql = 'Select * from Customer Order By Date ' + @asc_desc + ', Name';

EXEC sp_executesql @sql

This will order Date DESCENDINGand Name ASCENDING.

You need to add DESCif you want to use DESCENDINGbecause ASCENDINGby default.

Answer 2

SELECT 
  Customer_ID,                        
  Name,                               
  Age                                         
FROM
  #Customer
ORDER BY 
  CASE WHEN @field = 'Name' AND @direction = 'A' THEN Name ELSE NULL END ASC,
  CASE WHEN @field = 'Name' AND @direction = 'D' THEN Name ELSE NULL END DESC,
  CASE WHEN @field = 'Age'  AND @direction = 'A' THEN Age  ELSE NULL END ASC,
  CASE WHEN @field = 'Age'  AND @direction = 'D' THEN Age  ELSE NULL END DESC

. , somethign ...

SELECT 
  Customer_ID,                        
  Name,                               
  Age                                         
FROM
(
  SELECT
    Customer_ID,
    Name,
    ROW_NUMBER() OVER (ORDER BY Name) AS "name_order",
    Age,
    ROW_NUMBER() OVER (ORDER BY Age)  AS "age_order"
  FROM
    #Customer
)
  AS [data]
ORDER BY 
  CASE @field1
    WHEN 'Name' THEN CASE @direction1 WHEN 'A' THEN name_order ELSE -name_order END
    WHEN 'Age'  THEN CASE @direction1 WHEN 'A' THEN age_order  ELSE -age_order  END
    ELSE NULL
  END,
  CASE @field2
    WHEN 'Name' THEN CASE @direction2 WHEN 'A' THEN name_order ELSE -name_order END
    WHEN 'Age'  THEN CASE @direction2 WHEN 'A' THEN age_order  ELSE -age_order  END
    ELSE NULL
  END

, ...

: , , , .

Answer 3

SQL Server 2005+ :

WITH CustomerCTE AS (
  SELECT
    *,
    DateSort = ROW_NUMBER() OVER (ORDER BY Date),
    NameSort = ROW_NUMBER() OVER (ORDER BY Name)
  FROM Customer
)
SELECT *
FROM CustomerCTE
ORDER BY DateSort * @DateSortDir, NameSort * @NameSortDir

1, -1.

---

, , ORDER BY . , .

( ), .

DECLARE @IntSortDir int;
SET @IntSortDir = CASE @directionOfSort WHEN 'A' THEN 1 ELSE -1 END;

WITH CustomerCTE AS (
  SELECT
    Customer_ID,
    Name,
    Age,
    NameSort = ROW_NUMBER() OVER (ORDER BY Name),
    AgeSort  = ROW_NUMBER() OVER (ORDER BY Date)
  FROM Customer
)
SELECT
  Customer_ID,
  Name,
  Age
FROM CustomerCTE
ORDER BY
  CASE @fieldSort WHEN 'Age' THEN AgeSort END * @IntSortDir,
  NameSort * @directionOfSort,
  CASE @fieldSort WHEN 'Name' THEN AgeSort END * @IntSortDir

@fieldSortindicates a primary order column. The other automatically becomes secondary.

Answer 4

Here is the solution

Explanation:
1. Ordering the row number on the basis of SQL input param  order by (DESC, ASC)
2. Againt ordering the row number in outer query

Try this code (working)

DECLARE @PageNum int
DECLARE @PageSize int
DECLARE @TotalRowsNum int
DECLARE @SortColumn varchar(200)
DECLARE @SortOrder varchar(5)

SET @PageNum = 4;
SET @PageSize = 10;
SET @SortColumn = 'CODE_ID';
SET @SortOrder = 'DESC';

WITH QueryResult AS
(

SELECT *,
CASE @SortOrder WHEN 'ASC' THEN
ROW_NUMBER() OVER(ORDER BY @SortColumn ASC) 
ELSE
ROW_NUMBER() OVER(ORDER BY @SortColumn DESC) 
END AS 'RowNumber'

FROM TABLE_NAME 
) 
SELECT * FROM QueryResult
WHERE RowNumber BETWEEN (@PageNum - 1) * @PageSize + 1 AND @PageNum * @PageSize
ORDER BY RowNumber ASC