SQL SELECT Issue

[cotton31]

Hi,

I currently have the following data:

Category Amount EffectiveDate
Apple 10 10/06/2011
Apple 4 06/06/2011
Apple 13 01/06/2011
Banana 8 10/06/2011
Banana 6 06/06/2011
Banana 9 01/06/2011
Orange 14 10/06/2011
Orange 2 06/06/2011
Orange 8 01/06/2011

I need to get the most recent amount for each category, but I don't want the EffectiveDate column to be SELECTed.

So I couldn't have:

SELECT Category, Amount, MAX(EffectiveDate)

FROM Fruits

GROUP BY Category, Amount

I need the output to be:

Category Amount
Apple 10
Banana 8
Orange 14

How would I do this?

Edit: Forgot to say, as always, this is SQL Server 2005

 

[Engram]

Ok, this should work:

SELECT 
	f1.Category, f1.Amount
FROM 
	Fruits AS f1
INNER JOIN
	(
	SELECT 
		Category, MAX(EffectiveDate) AS EffectiveDate
	FROM 
		Fruits
	GROUP BY
		Category
	) AS f2
	ON f1.EffectiveDate = f2.EffectiveDate AND f1.Category = f2.Category

 

[dean_e_boy]

Another way would be:

SELECT Category, Amount
FROM Fruits a
where a.EffectiveDate = ( select max(b.EffectiveDate)
from Fruits b
where b.category = a.category)

Hope this helps

 

[Engram]

Another way would be:

SELECT Category, Amount
FROM Fruits a
where a.EffectiveDate = ( select max(b.EffectiveDate)
from Fruits b
where b.category = a.category)

Hope this helps

Nice; shorter than mine. I always seem to forget that you can reference aliases outside of the sub-select.

 

[cotton31]

Thanks both - dean, would your's work if I had different effective dates for each category? I know in my example, the latest one is the same date for each, but they could all be different dates in reality, just the latest one for that category.

Cheers,

Edit: Ignore me, should have tested your brilliant solution first. It works!

Thanks both for your help!