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:

result would be:

CodePudding user response：

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

CodePudding user response：

I am sure there is a more efficient way to do the query but I used a subquery. this should get you the desired result

SELECT DISTINCT m.[part], ad.x AS 'date', m.[loc]
FROM [MainTable] AS 'm'
LEFT OUTER JOIN 
  (SELECT MAX([date]) AS 'x', [part]
  FROM [MainTable]
  GROUP BY [part]) AS 'ad'
WHERE m.[part] = 123 --desired value

nbk's answer

There is the cleaner query.

CodePudding user response：

Nearly the same as TiltedTeapot's answer:

drop table if exists #temp;
create table #temp ([part] int, [date] date, [loc] nvarchar(10));
insert into #temp values
    (123, '20220801', 'store 1'),
    (123, '20220802', 'store 1'),
    (123, null, 'store 2'),
    (123, '20220803', 'store 3');

select distinct 
    t1.part,
    t1.loc,
    t2.date
from
    #temp t1
    left join
    (
        select 
            part,
            max([date]) as date
        from #temp 
        group by part
    ) t2
    on t1.part = t2.part;

(written for MS SQL Server)