SQL – Ordering query by lastest date and then by ID

I have a table that looks something like this:

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

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.

Advertisement

Answer