SQL Server processing for jquery datatables order by issue?

Question

I have written the following script to mimic the incoming parameters from datatables and try to filter the results using the parameters. Everything works fine except the order by clause. Basically it only orders by rownumber and does not take into consideration the case statement which provides the second order by column.

declare @sSortColumn as nvarchar(50)='Country';
declare @sSortDirection as nvarchar(5) = 'Desc';
declare @sSearch as nvarchar(50) = '';
declare @iDisplayLength as int = 20;
declare @iDisplayStart as int = 20;
declare @sIDsearch as int = CASE WHEN ISNUMERIC(@sSearch) = 1 THEN CAST(@sSearch AS INT) ELSE 0 END;

WITH media AS
(
   select ROW_NUMBER() OVER (ORDER BY mc.id) as RowNumber,
   mc.id,mc.Name, mc.CityID,lc.Name as Country
     from Lookup_MediaChannels mc
    left join Lookup_SonarMediaTypes st on mc.SonarMediaTypeID = st.ID
    left join Lookup_SonarMediaGroups sg on sg.ID = st.SonarMediaGroupID
    left join Lookup_MediaTypes mt on mc.MediaTypeID = mt.ID
    left join Lookup_SonarMediaGroups sg1 on sg1.ID = mt.MediaGroupID
    left join lookup_Countries lc on lc.id = mc.countryid
    where mc.Name like '%'+@sSearch+'%'
    and (sg1.ID=1 or sg.ID =1 )
    or mc.id =  @sIDsearch
) 

SELECT RowNumber, Name, Country
FROM media 
WHERE RowNumber BETWEEN (@iDisplayStart+ 1) AND (@iDisplayStart+ @iDisplayLength)

order by rownumber, 

CASE WHEN @sSortColumn = 'Name' AND @sSortDirection = 'Desc' 
THEN Name END DESC,

CASE WHEN @sSortColumn = 'Name' AND @sSortDirection != 'Desc' 
THEN Name END,

CASE WHEN @sSortColumn = 'Country' AND @sSortDirection = 'Desc' 
THEN Country END DESC,

CASE WHEN @sSortColumn = 'Country' AND @sSortDirection != 'Desc' 
THEN Country END

Perhaps you want the order by to sort by the case statements first then by rownumber? it looks like rownumber will be unique for each name anyway. — Ian Kenney, Jun 28 '13 at 09:18
the row number should be sorted first because it is used to retrieve a range of items between two numbers "WHERE RowNumber BETWEEN (@iDisplayStart+ 1) AND (@iDisplayStart+ @iDisplayLength)" — Farhad-Taran, Jun 28 '13 at 09:21
if you have data row_num|name 1|TV 2|Radio then if you order by row number first, ordering by name second will have no effect, you will always have TV before radio because row_num 1 comes before 2 — Ian Kenney, Jun 28 '13 at 09:26

score 1 · Accepted Answer · answered Jun 28 '13 at 09:44

This simplified example may help you

http://sqlfiddle.com/#!6/35ffb/10

Set up some dummy data (this would be replaced by your select statement)

create table x(
  id int,
  name varchar(3),
  country varchar(2)
)
insert into x 
values (1,'aaa','uk'),
(2,'aaa','us'),
(3,'baa','uk'),
(4,'caa','uk'),
(5,'baa','it')

Some vars to hold sort field and sort order

declare @so char(1)
declare @sf char(1)
set @so = 'a' -- a = asc  d = desc
set @sf = 'n' -- n = name c = country

and a select to return sorted data

  SELECT row_number() 
  OVER (order by 
          CASE WHEN @so =  'd' THEN sf END desc, 
          CASE WHEN @so <> 'd' THEN sf end,
          id
        ) AS aa, name,country
  FROM (
    SELECT x.*, case @sf when 'n' then name when 'c' then country end sf 
    FROM X
  ) X

SQL Server processing for jquery datatables order by issue?

1 Answers1