I have a table that looks something like this:
x
NotificationID NotificationTypeID CreatedOn
5 2 2020-01-27 10:05:33.147
4 13 2020-01-24 15:56:04.437
3 3 2020-01-24 14:16:53.327
2 2 2020-01-24 14:16:53.327
1 1 2020-01-22 15:12:38.663
I want it to be ordered by the CreatedOn field and then NotificationTypeID like this:
NotificationID NotificationTypeID CreatedOn
5 2 2020-01-27 10:05:33.147
2 2 2020-01-24 14:16:53.327
4 13 2020-01-24 15:56:04.437
3 3 2020-01-24 14:16:53.327
1 1 2020-01-22 15:12:38.663
My SQL looks like this:
SELECT
ROW_NUMBER() OVER (PARTITION BY Notification.NotificationTypeID ORDER BY CreatedOn DESC) RowNumber,
Notification.NotificationID,
Notification.NotificationTypeID,
CreatedOn
FROM Notification
ORDER BY RowNumber DESC, CreatedOn DESC
But it gives me the oldest value first:
NotificationID NotificationTypeID CreatedOn
2 2 2020-01-24 11:34:37.063
5 2 2020-01-27 10:05:33.147
4 13 2020-01-24 15:56:04.437
3 3 2020-01-24 14:16:53.327
1 1 2020-01-22 15:12:38.663
Advertisement
Answer
I suspect that you want:
select NotificationID, NotificationTypeID, CreatedOn
from Notification
order by
max(CreatedOn) over(partition by NotificationTypeID) desc,
CreatedOn desc
This will put first the NotificationTypeID
that has the greatest CreatedOn
, and then order records that have the same NotificationTypeID
by descending CreatedOn
.