sql query to get server side data -paging by date desc

Question

Hello All, I wrote a sql query to get custom paging in asp.net gridview. I am getting the result in gridview as expected but there is a discrepancy - record not in sorted in descending date. I need your help to find a solution to write below sql query in descending date order. SET NOCOUNT ON ; SELECT ROW_NUMBER() OVER ( ORDER BY [CustomerID] ASC ) AS RowNumber, tblActionDetaills.FID,tblActionDetaills.VID,tblActionDetaills.AdminUserID, tblImageDetails.Filepath, IsRead, Convert ( varchar (20),EventDate,103) as EventDate, REPLACE ( SUBSTRING ( CONVERT (nvarchar(128),EventDate, 109), 12 , 128), ':000' , ' ' ) as EventTime, Description INTO #Results FROM tblActionDetaills inner join tblVilla on tblVilla.VID=tblActionDetaills.VID inner join tblUserLogin on tblUserLogin.UserID=tblVilla.UserID left join tblImageDetails on tblImageDetails.FID=tblActionDetaills.FID WHERE tblUserLogin.UserID= 'AA01' order by Convert (datetime,EventDate,103) desc SELECT '10' = COUNT (*) FROM #Results SELECT * FROM #Results WHERE RowNumber BETWEEN (1 -1) * 10 + 1 AND (((1 -1) * 10 + 1) + 10) - 1 order by Convert (datetime,EventDate,103) desc DROP TABLE #Results

Mario Scheeren · Answer

Despite the fact, that my way would be the correct one in current MSSQL Servers ... SELECT ROW_NUMBER() OVER ( ORDER BY [CustomerID] ASC , Convert (datetime,EventDate,103) DESC ) AS RowNumber, All the other stuff ORDER BY RowNumber ASC

Bineeshcp Viswanath · Answer

Hi Mario, The latest code working as i expected. Thank you for the support..

Bineeshcp Viswanath · Answer

Dear Mario, This is to inform that I have set all my project based on the query i posted. So I used your query and it not working like the query I posted. So Can you alter my query to get the result in date descending order?

Mario Scheeren · Answer

How about this ? DECLARE @PageSize int = 10; DECLARE @PageNumber int = 1; SELECT tblActionDetaills.FID, tblActionDetaills.VID, tblActionDetaills.AdminUserID, tblImageDetails.Filepath, IsRead, Convert ( varchar (20),EventDate,103) as EventDate, REPLACE ( SUBSTRING ( CONVERT (nvarchar(128),EventDate, 109), 12 , 128), ':000' , ' ' ) as EventTime, Description FROM tblActionDetaills INNER JOIN tblUserLogin on tblUserLogin.UserID=tblVilla.UserID LEFT JOIN tblImageDetails on tblImageDetails.FID=tblActionDetaills.FID INNER JOIN tblVilla on tblVilla.VID=tblActionDetaills.VID WHERE tblUserLogin.UserID= 'AA01' ORDER BY Convert (datetime,EventDate,103) DESC OFFSET @PageSize * (@PageNumber - 1) ROWS FETCH NEXT @PageSize ROWS ONLY ;