Aggregate function

[Sql7user]

Hi

I have a problem with a aggregate function in a query, and that I hope someone can help me with.

The hole query is way more complex than this but i have cut out the problem part. I have 3 fields as below. As you can see the article field is not uniqe.

Article, Price, Date of Pricing
A01 3,95 040601
A01 2,90 040201
A02 4,90 040302
A02 3,50 040101

I want a result that for every article takes out the
highest date of pricing and what the price is.

The result should look like this:
A01 3,95 040601
A02 4,90 040302

I have tried the following query:
Select Article, Price, MAX(Date of Pricing) from X group by Article, Price

Of course all the article will come out as a result beqause the price field and that is what I want to know. Can I use another way to get the right result. As I said before the hole query is way more complex so I hope I can do it without a sub query or similiar but if thats the only way there is please help me with that to.

Thanks in advance

 

[SkipVought]

Hi,

Try this

SELECT distinct X1.Article, X1.Price, X1.`Date of Pricing`
FROM X X1
Where  X1.Article & cstr(X1.`Date of Pricing`) IN 
(SELECT X2.Article & CStr(Max(X2.`Date of Pricing`)) 
FROM X X2 
GROUP BY X2.Article)

Skip,

_{[red]Be advised:[/red] It's been reported that a wee psychic is roaming the countryside.
Small Medium @ Large!}

 

[Sql7user]

Nice, it worked perfectly SkipVought. Hope I can get it right in the real query but this solved the problem.

Thank you so much!