-2

Please help me with one task. I want to get all data above date. It`s might be smth like this: 1. Find max date in column last_date for every Product_Name. 2. Get all rows that max(last_date)>start_date for every Product_Name group

My table:

My_table1

And what I want to get:

Final_Resalt_table

Vasyl Mykhalyuk
  • 1
  • 1
  • 2
    MySQL <> SQL Server - please correct your tags. – Dale K Jul 28 '21 at 07:15
  • Please, read this: [Ask]. You have to explain what is wrong with your SQL query and define above what date you want to select data. – Maciej Los Jul 28 '21 at 07:20
  • It`s might be smth like this: 1. Find max date in column last_date for every Product_Name. 2. Get all rows that max(last_date)>start_date for every Product_Name group. – Vasyl Mykhalyuk Jul 28 '21 at 07:24

1 Answers1

0

If I understand correctly, you can use window functions:

select t.*
from (select t.*,
             max(last_date) over (partition by product_name) as max_last_date
      from t
     ) t
where start_date > max_last_date;

Note that I changed the order of the comparison to match the results you specify rather than what the question describes.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • yeap, this is what i need. Thx a lot. I guess current sql server is not allow me to use window functions. Is it possible to do that without it? – Vasyl Mykhalyuk Jul 28 '21 at 11:20
  • @VasylMykhalyuk . . . Window functions have been part of standard SQL for a long, long time and pretty much all (real) databases support them. What database are you using under the hood? – Gordon Linoff Jul 28 '21 at 12:03