aggregate function 3

[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

 

[SQLSister]

Not sure exactly what you are getting at here. If you mean how to use this with the rest of your data that you do not want in the group by statment, then a derived table with this statment is the way to go. Someting like:

Select table1.field1, table1.field2, a.article, a.MaxpriceDate, a.Price 
from table1
join
(Select Article, Price, MAX(PriceDate) as MAxPriceDate from table1 group by Article, Price) a
on table1.Article = a.Article and table1.PriceDAte = a.MaxPriceDate

Of course there can be other joins etc going on. The key to using a derived table is to make sure you give it an alias (in this case I used "a"). Then you can refer to it just like any other table in the rest of the query. Also make sure you alias anny aggreated fields so you have a field name to refer to.

Questions about posting. See faq183-874

 

[rac2]

In general prepare a query which gives the aggregate result for each item. Then join that to the base table to obtain all of the details about the item. I suppose this entails a subquery but no fear, thats what SQL is for. Sometimes a subquery can be stored as a view. That is a good approach where it will be used in more than one query.

SELECT Article, MAX(DateOfPricing)
FROM Goodies
GROUP BY Article

That query identifies the date and can be used to retrieve other details in the table by joining it to the base table.

SELECT Goodies.Article, Goodies.Price, Goodies.DateOfPricing
FROM Goodies
JOIN
( SELECT Article, MAX(DateOfPricing) AS "DateRecentPricing"
  FROM Goodies
  GROUP BY Article
) LatestPricing
   ON LatestPricing.DateRecentPricing = Goodies.DateOfPricing
   AND LatestPricing.Article = Goodies.Article

Lets face it. We want an aggregate value from the table. Aggregates come from subqueries. That is the price we pay.

 

[Sql7user]

Thanks both of you! Your really helped me out there!

/Sql7user