sql - Pulling all rows with the date that is directly less than the date of interest

Question

I currently have a table that has these fields:

Name	Date	Flag
John	5-28-10	N
John	5-28-10	N
John	5-28-10	N
John	5-29-10	Y
Rick	7-14-12	N
Rick	9-02-13	N
Rick	4-21-15	Y
Rick	6-21-17	N

question from:https://stackoverflow.com/questions/65924359/pulling-all-rows-with-the-date-that-is-directly-less-than-the-date-of-interest

Answer 1

You can use dense_rank as follows:

Select t.* from
(Select t.*,
       dense_rank() over (partition by name order by date desc) as dr
  From your_table t
 Where t.date < (select min(tt.date)
                from your_table tt
                where tt.name = t.name 
                  and tt.flag = 'Y') ) t
  Where t.dr = 1