CODEWITHSUNDEEP
sqlms-access-2007
WhatEvil
WhatEvil

Reputation: 481

SQL SELECT only rows where a max value is present, and the corresponding ID from another linked table

I have a simple Parts database which I'd like to use for calculating costs of assemblies, and I need to keep a cost history, so that I can update the costs for parts without the update affecting historic data.

So far I have the info stored in 2 tables:

tblPart:

PartID | PartName
1      | Foo
2      | Bar
3      | Foobar

tblPartCostHistory

PartCostHistoryID | PartID | Revision | Cost
1                 | 1      | 1        | £1.00
2                 | 1      | 2        | £1.20
3                 | 2      | 1        | £3.00
4                 | 3      | 1        | £2.20
5                 | 3      | 2        | £2.05

What I want to end up with is just the PartID for each part, and the PartCostHistoryID where the revision number is highest, so this:

PartID | PartCostHistoryID
1      | 2
2      | 3
3      | 5

I've had a look at some of the other threads on here and I can't quite get it. I can manage to get the PartID along with the highest Revision number, but if I try to then do anything with the PartCostHistoryID I end up with multiple PartCostHistoryIDs per part.

I'm using MS Access 2007.

Many thanks.

Upvotes: 1

Views: 1318

Answers (3)

Gord Thompson
Gord Thompson

Reputation: 123484

Mihai's (very concise) answer will work assuming that the order of both

  • [PartCostHistoryID] and

  • [Revision] for each [PartID]

are always ascending.

A solution that does not rely on that assumption would be

SELECT 
    tblPartCostHistory.PartID, 
    tblPartCostHistory.PartCostHistoryID
FROM
    tblPartCostHistory
    INNER JOIN
    (
        SELECT
            PartID,
            MAX(Revision) AS MaxOfRevision
        FROM tblPartCostHistory
        GROUP BY PartID
    ) AS max
        ON max.PartID = tblPartCostHistory.PartID
            AND max.MaxOfRevision = tblPartCostHistory.Revision

Upvotes: 2

Akshay
Akshay

Reputation: 1901

Here is query

select PartCostHistoryId, PartId from tblCost
where PartCostHistoryId in 
(select PartCostHistoryId from 
   (select * from tblCost as tbl order by Revision desc)  as tbl1
group by PartId
)

Here is SQL Fiddle http://sqlfiddle.com/#!2/19c2d/12

Upvotes: 0

Mihai
Mihai

Reputation: 26784

SELECT PartID,MAX(PartCostHistoryID) FROM table GROUP BY PartID

Upvotes: 0

Related Questions