Using MAX per another field

[tonykblen]

Hi,

I have query like this:

select isx_transaction,isx_item,isx_itemspec,sum(isx_materialcost)
from itemspecbomw
group by isx_item,isx_itemspec,isx_transaction
order by isx_item,isx_itemspec,isx_transaction

which returns:

Tr. Num Code Spec Cost
44300 FCH0001 1 1.87042
45258 FCH0001 1 1.87042
45260 FCH0001 1 1.87042
45626 FCH0001 1 1.87042
70235 FCH0001 1 1.91092
146190 FCH0001 1 4.28908
149833 FCH0001 1 4.28908
149839 FCH0001 1 4.28908
149846 FCH0001 1 4.28908
146190 FCH0003 1 5.06532
149788 FCH0003 1 5.00212
149833 FCH0003 1 5.00212
149839 FCH0003 1 5.00212
149846 FCH0003 1 5.00212
151202 FCH0003 1 5.00212


What I want to do is single out the max Tr.Num per code? I keep trying different things, but end up in a muddle.

Any help greatly appreciated.

Tony.


Tony Kennedy BSc. B.I.S.,
MCSA Cand.

A good start is half the work.
Every start is difficult .
-Two Gaelic proverbs

 

[SQLBill]

Try this:

SELECT DISTINCT
isx_transaction
,MAX(isx_item)
FROM itemspecbomw
GROUP BY
isx_transaction
ORDER BY
isx_transaction

-SQLBill

 

[SQLBill]

Okay, I got the columns wrong...

SELECT DISTINCT
isx_item
, MAX(isx_transaction)
FROM itemspecbomw
GROUP BY
isx_item
ORDER BY
isx_item ASC

-SQLBill

 

[swampBoogie]

select isx_transaction,isx_item,isx_itemspec,sum(isx_materialcost)
from itemspecbomw q
where isx_transaction
in (select max(isx_transaction)
from itemspecbomw 
where isx_item = q.isx_item)
group by isx_item,isx_itemspec,isx_transaction
order by isx_item,isx_itemspec,isx_transaction

 

[tonykblen]

Hi again,

SwampBoogie thats worked a treat but a little explanation as to what the query does would be great. The use of an alias for the inner select I can see but can you elaborate on that please?

Many thanks to you and SQLBill,

Tony.

Tony Kennedy BSc. B.I.S.,
MCSA Cand.

A good start is half the work.
Every start is difficult .
-Two Gaelic proverbs