Search code examples
sqlmax

SQL MAX: max date from multiple locations same part

what I'm looking to find is that last or max date a part number was purchased from any store. so we can have so sales or sales and just give the max date:

part date loc
123 8/1/2022 store 1
123 8/2/2022 store 1
123 null store 2
123 8/3/2022 store 3

result would be:

part date Loc
123 8/3/2022 store 1
123 8/3/2022 store 2
123 8/3/2022 store 3
Solution

  • Select the max date in a subquery for every part, it would give you one Result, the highest date.

    The Query should work with most rdms

    SELECT DISTINCT [part], (SELECT MAX([date]) FROM Table1 WHERE part = t1.part) [Date],[loc] FROM Table1 t1

    part | Date     | loc    
---: | :------- | :------
 123 | 8/3/2022 | store 1
 123 | 8/3/2022 | store 2
 123 | 8/3/2022 | store 3

    db<>fiddle here