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

Bineeshcp Viswanath

7y1.2k

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

4 Replies

Know the answer? Post it — somebody with the same question will find it here.

Mario Scheeren7y ago
Accepted 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
+1
Bineeshcp Viswanath7y ago
Hi Mario,

The latest code working as i expected. Thank you for the support..
0
Bineeshcp Viswanath7y ago
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?
0
Mario Scheeren7y ago
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;
0