August 31, 2011 at 2:33 am
Hi all, my first post here, hoping someone could help me out on the following problem.
I have this query where sorting needs to be achieved (as well as paging, etc), depending on a value (sortcolumn) that comes in as a parameter with the sproc call, but NULL values should always be at the bottom...
I know the NULL issue can be resolved in SQL with the use of the case statement:
ORDER BY (case WHEN [fieldname] IS NULL THEN 1 ELSE 0 END), [fieldname] ASC
but how do I correctly combine it with my case statement on the sortcolumn?
The best that I could make up and that compiles is something like this, but that gives me the wrong types
DECLARE @Result TABLE(Id int, Row bigint)
INSERT INTO @Result
SELECT
dt.Id,
ROW_NUMBER() OVER
(
ORDER BY
CASE WHEN @SortColumn = '' or @SortColumn is null THEN dt.Name END ASC,
CASE WHEN @SortColumn = 'Name' THEN dt.Name (CASE WHEN [dt.Name] IS NULL THEN 1 ELSE 0 END) END,
CASE WHEN @SortColumn = 'Name DESC' THEN dt.Name (CASE WHEN [dt.Name] IS NULL THEN 0 ELSE 1 END) END,
CASE WHEN @SortColumn = 'Code' THEN dt.Code (CASE WHEN [dt.Code] IS NULL THEN 1 ELSE 0 END) END,
CASE WHEN @SortColumn = 'Code DESC' THEN dt.Code (CASE WHEN [dt.Code] IS NULL THEN 0 ELSE 1 END) END,
CASE WHEN @SortColumn like '%%' THEN dt.Name END ASC
) Row
FROM
[DBTable] dt
WHERE
dt.IsDeleted = 0 AND
(@Name is null or dt.Name like '%' + @Name + '%')
SET @TotalCount=(SELECT COUNT(*) FROM @Result)
SELECT
dt.Id as Id,
dt.Name as Name,
dt.Code as Code
FROM
@Result result
INNER JOIN
[DBTable] dt on dt.Id = result.Id
WHERE
result.Row BETWEEN @startRowIndex AND (@StartRowIndex+@PageSize-1)
ORDER BY
result.Row
Anyone that could fix my query so I'm getting the correct results back in my temptable?
Big thanks in advance!
August 31, 2011 at 4:09 am
You're on the right track, just make sure that every WHEN brach returns the same data type.
dt.Name appears to be a char/varchar column, so I guess that changing 1 into '1' and 0 into '0' should do the trick.
Hope this helps
Gianluca
-- Gianluca Sartori
August 31, 2011 at 5:36 am
Problem is if I go with the suggested approach, then I will have to make varchar columns for example 'zzzzzzzzzzzzzz' in the inner case statement so they would be sorted at the bottom. I was looking for a neater solution, where I can order on two colums (1/0 first, depending on the outcome of the IS NULL case, and the Field value itself as second sort)
That way I'm not depending on maxvalues to make this sorting work.
But how do I put those 2 order by statements correctly in my outer case of my query?
August 31, 2011 at 6:01 am
Problem is if I go with the suggested approach, then I will have to make varchar columns for example 'zzzzzzzzzzzzzz' in the inner case statement
You're right. You can use REPLICATE(<maximum unicode char>, <maximum length of the colum>).
<maximum unicode char> can be retrieved with NCHAR(65535)
<maximum length of the column> can be retrieved with COLUMNPROPERTY(OBJECT_ID('TableName'),'ColumnName','Precision')
But how do I put those 2 order by statements correctly in my outer case of my query?
The tricky part is coding the ASC/DESC sorting, that can be done feeding the ASC sort with a NULL value in case we need the DESC sort.
DECLARE @Result TABLE(Id INT, Row BIGINT)
INSERT INTO @Result
SELECT
dt.Id,
ROW_NUMBER() OVER
(
ORDER BY
CASE
WHEN @SortColumn = '' OR @SortColumn IS NULL THEN dt.Name
WHEN @SortColumn = 'Name' THEN ISNULL(dt.Name,REPLICATE(NCHAR(65535),COLUMNPROPERTY(OBJECT_ID('DBTable'),'Name','Precision'))
WHEN @SortColumn = 'Code' THEN ISNULL(dt.Code,REPLICATE(NCHAR(65535),COLUMNPROPERTY(OBJECT_ID('DBTable'),'Code','Precision'))
WHEN @SortColumn LIKE '%%' THEN dt.Name
-- "Name DESC" and "Code DESC" fall into the deafault branch ==> NULL
END ASC,
CASE
WHEN @SortColumn = 'Name DESC' THEN ISNULL(dt.Name,REPLICATE(NCHAR(65535),COLUMNPROPERTY(OBJECT_ID('DBTable'),'Name','Precision'))
WHEN @SortColumn = 'Code DESC' THEN ISNULL(dt.Code,REPLICATE(NCHAR(65535),COLUMNPROPERTY(OBJECT_ID('DBTable'),'Code','Precision'))
END DESC
) Row
FROM
[DBTable] dt
WHERE
dt.IsDeleted = 0 AND
(@Name IS NULL OR dt.Name LIKE '%' + @Name + '%')
SET @TotalCount=(SELECT COUNT(*) FROM @Result)
SELECT
dt.Id AS Id,
dt.Name AS Name,
dt.Code AS Code
FROM
@Result result
INNER JOIN
[DBTable] dt ON dt.Id = result.Id
WHERE
result.Row BETWEEN @startRowIndex AND (@StartRowIndex+@PageSize-1)
ORDER BY
result.Row
Hope this helps
Gianluca
-- Gianluca Sartori
August 31, 2011 at 11:05 am
Easiest to have three sort fields, I would say.
One to sort the non-Nulls before the Nulls, and one each for the Ascending and Descending sorts:
;with s as (
SELECTdt.id
,dt.code
,dt.name
,casewhen@sortColumn in ('Name','Name DESC') and dt.name is null
then1
when@sortColumn in ('Code','Code DESC') and dt.code is null
then1
else0
end as [NullSort]
,casewhen@SortColumn = 'Name'
thendt.Name
when@SortColumn = 'Code'
thendt.Code
elsenull
end as [SortAsc]
,casewhen@SortColumn = 'Name DESC'
thendt.Name
when@SortColumn = 'Code DESC'
thendt.Code
elseNull
end as [SortDesc]
FROM[DataTable] dt
WHEREdt.IsDeleted = 0
AND(@Name is null or dt.Name like '%' + @Name + '%')
)
,r as (
selects.id
,s.code
,s.name
,row_number() over (order by [NullSort], [SortAsc], [SortDesc] DESC) as N
froms
)
SELECTr.id
,r.Name as Name
,r.Code as Code
,max(r.N) over () as TotalCount
FROMr
WHEREr.N BETWEEN @startRowIndex AND (@StartRowIndex+@PageSize-1)
ORDER BYr.N
;
August 31, 2011 at 12:46 pm
It would be simpler to use dynamic SQL and just construct the exact ORDER BY clause that you need.
September 1, 2011 at 1:12 am
Michael Valentine Jones (8/31/2011)
It would be simpler to use dynamic SQL and just construct the exact ORDER BY clause that you need.
Agreed. I'm a big fan of dynamic SQL. I have seen people doing incredibly complicated things to avoid dynamic sql, just because "it's bad".
Erland Sommarskog has a nice article on the subject.
-- Gianluca Sartori
September 2, 2011 at 8:15 pm
Michael Valentine Jones (8/31/2011)
It would be simpler to use dynamic SQL and just construct the exact ORDER BY clause that you need.
+1
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 8 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply