Tag Archives: MX.3

oracle – lead/lag functions returning inconsistent results [solved]

When using oracle lead() and lag() functions, I faced “inconsistent” results where running same query twice was returning different values for lead()/lag() calls. select reference_id, field1, lead(field1 1, 0) over (partition by col1, col2 order by STATUS_TIME_LONG)  next_val; This simply came from fact that my sorting key was not unique! Though STATUS_TIME_LONG recoreded entry timestamp in milliseconds, several entries within my grouping still shared same timestamp value for this field. Solution to get consistent output for lead/lag functions is simply to ensure unique-id field is part of sorting key. In my case, field reference_id is unique auto-incrementing field, so  perfect for […]