Order By Asc and Desc both in single query

user1037747 Published at Dev

user1037747

I have a query which retrieves latest data from table with pagination which is working fine.

But once the data is older from current time it should also appear but after the latest one.

SELECT A.UserId,A.FirstName,A.LastName,A.PostDate
(SELECT ROW_NUMBER() OVER(ORDER BY CAST(M.PostDate AS DATETIMEOFFSET) DESC) AS 'RowNumber'
    M.UserId,
   M.FirstName,
            M.LastName,
    M.PostDate
    FROM Messages AS M
   Where M.PostDate >= GetDate()
) A
       WHERE A.RowNumber BETWEEN @RowStart AND @RowEnd 
 ORDER BY CAST(A.PostDate AS DATETIMEOFFSET)  DESC

jlee-tessik

I'm not sure if I'm exactly getting what you're looking for, but this should get you something close. I created a CTE with two computed columns:

BeforeAfter, which determines if the date happens before or after the passed in date
AbsDiff, which gives the absolute value of the date diff.

Then, I just sort using those two fields:

DECLARE @current datetime = GETDATE()
;WITH cteMessages AS 
(
    SELECT UserId, FirstName, LastName, PostDate, 
    CASE WHEN PostDate < @current THEN 1 ELSE 0 END AS BeforeAfter,
    ABS(DATEDIFF(SECOND, @current, PostDate)) AS AbsDiff
    FROM Messages
)
SELECT * FROM cteMessages
ORDER BY BeforeAfter, AbsDiff

From those results, you can see how they are sorted first by messages newer than the passed in date, then in reverse order from older messages. You can substitute that order by into your Row_Number function.

Collected from the Internet

Please contact [email protected] to delete if infringement.