Bineeshcp Viswanath

Bineeshcp Viswanath

  • NA
  • 140
  • 39.8k

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

May 21 2019 3:16 AM
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. 
  1. SET NOCOUNT ON;  
  2. SELECT ROW_NUMBER() OVER  
  3. (  
  4. ORDER BY [CustomerID] ASC  
  5. )AS RowNumber,  
  6. tblActionDetaills.FID,tblActionDetaills.VID,tblActionDetaills.AdminUserID,  
  7. tblImageDetails.Filepath, IsRead,  
  8. Convert(varchar(20),EventDate,103) as EventDate,  
  9. REPLACE(SUBSTRING(CONVERT(nvarchar(128),EventDate, 109), 12 , 128),':000'' 'as EventTime,  
  10. Description INTO #Results FROM tblActionDetaills inner join tblVilla on tblVilla.VID=tblActionDetaills.VID  
  11. inner join tblUserLogin on tblUserLogin.UserID=tblVilla.UserID  
  12. left join tblImageDetails on tblImageDetails.FID=tblActionDetaills.FID  
  13. WHERE tblUserLogin.UserID='AA01'  
  14. order by Convert(datetime,EventDate,103) desc  
  15. SELECT '10' = COUNT(*)  
  16. FROM #Results  
  17. SELECT * FROM #Results  
  18. WHERE RowNumber BETWEEN(1 -1) * 10 + 1 AND(((1 -1) * 10 + 1) + 10) - 1  
  19. order by Convert(datetime,EventDate,103) desc  
  20. DROP TABLE #Results 

Answers (4)