SELECT ITEMS.ITEM, PRICES.PRICE, PRICES.DATE FROM ITEMS LEFT JOIN (SELECT MAX(DATE) AS THEDATE, ITEM FROM PRICES GROUP BY DATE, ITEM) ON ITEMS.ITEM = PRICES.ITEM

quote:

---

Originally posted by Dmh188

what about

SELECT ITEMS.ITEMS, PRICE.PRICE, MAX(PRICE.DATE) FROM ITEMS
INNER JOIN PRICE
ON PRICE.ITEM=ITEMS.ITEM
GROUP BY ITEMS.ITEM, PRICE.PRICE


sorry i have nothing close to this to test on. just going off the top of my head

---

SELECT
	a.item,
	b.date,
	b.price
FROM
	Items a
	OUTER APPLY
	(
		SELECT TOP 1 p.date,p.price 
		FROM Prices p 
		WHERE p.item = a.item 
		ORDER BY p.date DESC
	) b;

SELECT
	s.item,
	s.date,
	s.price
FROM
(
	SELECT
		a.Item,
		b.Price,
		b.date,
		ROW_NUMBER() OVER (PARTITION BY a.Item ORDER BY b.date DESC) AS RN
	FROM
		Items a
		LEFT JOIN Prices p ON 
			a.item = p.item
) s
WHERE s.RN = 1;

quote:

---

Originally posted by sunitabeck

Unless you have specific requirement to use LEFT JOIN, either of the following should work.

SELECT
	a.item,
	b.date,
	b.price
FROM
	Items a
	OUTER APPLY
	(
		SELECT TOP 1 p.date,p.price 
		FROM Prices p 
		WHERE p.item = a.item 
		ORDER BY p.date DESC
	) b;

OR

SELECT
	s.item,
	s.date,
	s.price
FROM
(
	SELECT
		a.Item,
		b.Price,
		b.date,
		ROW_NUMBER() OVER (PARTITION BY a.Item ORDER BY b.date DESC) AS RN
	FROM
		Items a
		LEFT JOIN Prices p ON 
			a.item = p.item
) s
WHERE s.RN = 1;

---

CREATE TABLE #Price ([Item] char(1), [Price] int, [Date] date)
GO

INSERT INTO #Price([Item], [Price], [Date])
SELECT 'A', 2, '1/1/2011' UNION ALL
SELECT 'A', 3, '5/1/2011' UNION ALL
SELECT 'A', 1, '12/25/2011' UNION ALL
SELECT 'B', 6, '12/5/2011' UNION ALL
SELECT 'B', 7, '12/6/2011'

SELECT * 
  FROM #Price o
 WHERE EXISTS (SELECT * 
				FROM #Price i
			   WHERE o.Item = i.Item
			GROUP BY Item
			  HAVING o.[Date] = MAX(i.[Date]))
GO