Max Dates For Cost Query

Hi,

I am trying to identify the costs for products with the latest cost date. I am unable to run the query. Is there something that I can do?

SELECT PART,COST, DATE FROM DATA/COSTFILE AS T1 WHERE T1.DATE= (SELECT MAX(DATE) AS T2 FROM DATA/COSTFILE AS T2 WHERE T2.PART=T1.PART)

Thanks,

DavidOriginally posted by hidvegi
Hi,

I am trying to identify the costs for products with the latest cost date. I am unable to run the query. Is there something that I can do?

SELECT PART,COST, DATE FROM DATA/COSTFILE AS T1 WHERE T1.DATE= (SELECT MAX(DATE) AS T2 FROM DATA/COSTFILE AS T2 WHERE T2.PART=T1.PART)

Thanks,

David

SELECT
T1.PART,
T1.COST,
T1.DATE
FROM
[DATA/COSTFILE] AS T1
WHERE
T1.DATE= (
SELECT MAX(T2.DATE) AS MAXDATE
FROM DATA/COSTFILE AS T2
WHERE T2.PART=T1.PART)

FYI, you shouldn't use ANY special characters in your column names or table names, especially "/\-_.,&%@.+"=".

The only exception to this is "_" which can be used after for sp_name on stored procedures that must reside in master and run on every database.

Max Count(*) question

I have a max(count(*)) sort of question.

Table ACCOUNT_PRODUCTS has:

ACCOUNT_PRODUCT_ID, ACCOUNT_NUMBER, PRODUCT_ID

Table PRODUCTS has:

PRODUCT_ID, PRODUCT_NAME, PRODUCT_CLASS

I need to get back a result set of ACCOUNT_NUMBERS and the PRODUCT_CLASS that is the one that is represented most often for the ACCOUNT_NUMBER.

I.e.,

Acct1 has 3 products in Class "Q", 2 in Class "W" and 5 in Class "C"
Acct2 has 1 product in Class "V" and 1 in Class "S"

Results Set should pick first product class if the count is the same and return:

Acct1, C
Acct2, V

I am baffled!

Thanks in advance,

Steve.Break the problem into logical steps:

1) Get the counts by account and class:

select account, class, count(*) cnt
from ...
group by account, class;
2) Get the max count per account:

select account, max(cnt) maxcnt
from
( select account, class, count(*) cnt
from ...
group by account, class
)
group by account;
3) Get details of account and class for those max(cnt) values:

select account, class
from
( select account, class, count(*) cnt
from ...
group by account, class
)
where (account, cnt) in
( select account, max(cnt) maxcnt
from
( select account, class, count(*) cnt
from ...
group by account, class
)
group by account
);
Now that does look messy. If your DBMS supports the WITH clause then you can rewrite as:

with temp as
( select account, class, count(*) cnt
from ...
group by account, class
)
select account, class
from temp
where (account, cnt) in
( select account, max(cnt) maxcnt
from temp
group by account
);