how to order 2 SQL Fields in asc and desc dynamically
I want to Order a SQL Select Query where there's 2 fields that are in the order by. I 开发者_开发知识库then need to decide if one is Descending and the other as Ascending. How is this done
I want something like:
Select * from Customer
Order By Date @asc_or_Desc_date, Name @asc_or_Desc_name
Anyone got any ideas?
I have 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
Anyone know how to sort this?
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
I wouldn't want to do that over many different combinations though. If you have a lot of combinations I'd do somethign based on the following...
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
Repeat as many times as is required...
Note: Just because it can be done this way, doesn't mean it should be done this way.
You will have to create your SQL statement dynamically in order to use a 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 DESCENDING
and Name ASCENDING
.
You only need to add DESC
if you want to use DESCENDING
as ASCENDING
is default.
In SQL Server 2005+ you could employ the following device:
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
The vars in this case should be either 1
or -1
.
EDIT
The additionally posted example seems to imply that the order of columns to use in ORDER BY should be dynamical too. And it also now seems that the order direction is specified uniformly for both columns.
Whether it is so or not (the question has become a bit more ambiguous), both are assumed in my second solution.
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
@fieldSort
specifies the primary order column. The other one automatically becomes the secondary one.
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
精彩评论