SELECT	d.*
FROM	DetailsTable AS d
WHERE	d.ReferenceNo = (SELECT TOP 1 m.ReferenceNo FROM MasterTable AS m WHERE m.ReferenceNo = d.ReferenceNo ORDER BY m.TransactionDate DESC)

-- prepare sample data
declare @master table
(
	ID int,
	referenceno varchar(20),
	TransactionDate datetime
)

declare @details table
(
	ID int,
	ReferenceNo varchar(20),
	ItemCode varchar(10),
	ItemCost numeric(25,2)
)

insert @master
select 1, 'ABC-123', '01/01/2007' union all
select 2, 'ABC-456', '01/02/2007' union all
select 3, 'ABC-789', '01/03/2007'

insert @details
select 1, 'ABC-123', '00001', 10.00 union all
select 2, 'ABC-123', '00002', 12.00 union all
select 3, 'ABC-456', '00001', 11.00 union all
select 4, 'ABC-456', '00003', 15.00 union all
select 5, 'ABC-456', '00004', 14.00 union all
select 6, 'ABC-789', '00001', 9.00 union all
select 7, 'ABC-789', '00004', 15.00 union all
select 8, 'ABC-789', '00005', 8.00

-- final query
select t1.ItemCode, t1.ItemCost
from @details t1 join @master t2 on t1.referenceno = t2.referenceno
Join
(
	select d.ItemCode, max(TransactionDate) as TransactionDate
	from @details d join @master m
	on d.referenceno = m.referenceno
	group by d.ItemCode
) t3
on t1.ItemCode= t3.ItemCode and 
t2.TransactionDate = t3.TransactionDate
order by 1

SELECT		d.*
FROM		@Details AS d
WHERE		d.ID = (SELECT TOP 1 q.ID FROM @Master AS m INNER JOIN @Details AS q ON q.ReferenceNo = m.ReferenceNo WHERE q.ItemCode = d.ItemCode ORDER BY m.TransactionDate DESC)
ORDER BY	d.ItemCode

quote:

---

But, if you have paid attention to my question, you might have given me a working solution.

---

quote:

---

Originally posted by DonAtWork

quote:

---

But, if you have paid attention to my question, you might have given me a working solution.

---

This makes me want to type out every swear word I know.

---