SQL - Compare current row date value with ALL previous rows dates

Question

Assume I have an End Date in a table

End Date
1/1/2018
5/1/2017
6/1/2017
12/31/2020

I need to compare each row's date with all previous rows date and set a flag or value if the current row date is less than any of the previous rows dates

In this case, row number 3 is greater than row number 2 but it is less than row number 1. Also row number 2 is less than row number 1. Row number 4 is greater than all the other rows

So the result should be something like this

End Date    Flag
1/1/2018     NULL ( Since no previous values to compare)
5/1/2017     Y
6/1/2017     Y
12/31/2020   N

I tried ROWNUMBER and LAG, I was only able to compare just the previous row. Not ALL the previous rows in the table. Please advise

previous rows in which order? Is there a column like an id that defines the order? — forpas
After editing your question to make it readable, I realize you need an ordering column. SQL is unordered and it is up to the engine to present the data in an order that is the fastest for it. It is not predictable. — Gabriel Durac

dnoeth dnoeth · Accepted Answer · 2020-08-24T10:09:03

I tried ROWNUMBER and LAG

This means your DBMS supports Windowed Aggregates, now you just need something to ORDER BY to get the order shown in your example:

case -- Since no previous values to compare
  when max(datecol)
       over (order by ????
             rows bweteen unbounded preceding and 1 preceding) is null
  then NULL

  --current row date is less than any of the previous rows dates
  when datecol 
     < max(datecol)
       over (order by ????
             rows bweteen unbounded preceding and 1 preceding)
     then 'Y'
     else 'N' 
  end

SQL - Compare current row date value with ALL previous rows dates

2 Answers