Saturday, March 3, 2018

Coding digest (SQL): Order by CreatedDateTime And ID Descending

I am creating a timeline of events right now in descending order. We have the events inside a table called timeline table. If the two events have the same CreatedDateTime, they should be ordered by their IDs (primary key which is a identity).

 So let's write a query for that.
(Assuming X is the current datetime)
SELECT * FROM TIMELINETABLE WHEN CREATEDDATETIME < X
ORDER BY DATETIME DESC, ID DESC

Is this right?
It is right but it won't work for me. Because, I am populating timeline items 10 at a time. And once the user scrolls to the bottom of the page, I will display 10 more events. For that I need to pass the last datetime and ID displayed on screen.

(Assuming X is the datetime and Y is the ID)
SELECT TOP 10  * FROM TIMELINETABLE WHEN CREATEDDATETIME < X OR (DATETIME = X AND ID < Y)
ORDER BY DATETIME DESC

No comments:

Post a Comment