Get next, higher value based on current value

Question

I'm trying to get the next audit id from a log but am getting multiple NextAuditID's for each AuditID.

A couple of questions:

1. how can this be corrected?
2. is this the most efficient approach?

My code:

SELECT DISTINCT 
    AuditID, MIN(NextAuditID) NextAuditID
FROM
    (SELECT 
         SMStart.AuditID AuditID, SMNext.AuditID NextAuditID
     FROM 
         SchedulerMetrics SMStart,
         SchedulerMetrics SMNext
     WHERE 
         SMStart.Message = SMNext.Message
         AND SMNext.AuditID > SMStart.AuditID) tt
GROUP BY 
    AuditID, NextAuditID

Answer 1

You can use LEAD window function:

SELECT AuditID, LEAD(AuditID) OVER (PARTITION BY Message ORDER BY AuditID) AS NextAuditID
FROM SchedulerMetrics