sql server mseq: I am currently working with 3 tables.

Table1 has records that have an effective time stamp according to a store number, or ALL stores.
Table1(RetailID, effectiveDate, storeNum, price)
Table2(RetailID, SKUID, vendorID)
Table3(SKUID, Size, desc)

I am currently working on a way to get the SKUID, desc, vendorID and then the effectiveDate (1 record for each skuid).

The effectivedate can be given to a particular store or to all stores ('0' for all stores) So given a storenumber I need to query and find that store's max(effectivedate) or the max(effectivedate for ALL stores '0').

Any suggestions on using a subquery, or temp tables? I have tried getting the Max(effectivedate) record for each storenum and retailID and insert into a sql table, but that doesn't seem to save me any resources.

Any ideas would be greatly appreciated.

Hi kda,

Pls post some sample records ,expected result set, pks/fks between the tables.

See following example as well,which will return rows from orders table for each
customerid's row from the same table based on the latest order (based on max of orderdate)
he has placed.

use northwind
go
select a.*
from orders a join
(select customerid, max(orderdate) orderdate
from orders group by customerid) b
on a.customerid = b.customerid and a.orderdate=b.orderdate
order by 2
go
-- OR --
select a.*
from orders a
where customerid in
(Select top 1 customerid from orders b
where b.customerid = a.customerid
group by b.customerid having max(b.orderdate) = a.orderdate )
order by 2
go

-- Vishal