I don't understand what makes "Part4" be the "Latest".
Or are you saying that "Latest" *is* a field in the DB?
If so, then your table structure is just fine. You don't have to "query the Part".
Just do:
Code:
SELECT T2.* FROM table AS T1, table AS T2
WHERE T1.ChainID = T2.ChainID
AND T1.Part = 'Part2'
AND T2.Latest = 'Y'
Yes, it works even if you are looking for 'Part4'.
Just a simple "self join".
Naturally, you would need ChainID to be indexed for decent performance. And you'd probably want Part to be indexed as well.
If all the values in Part for a given ChainID are different, then in fact you could make the combination of ChainID and Part be your primary key, and then it would be really efficient.
Come to think of it, even if they aren't unique, you still might get better performance using a non-unique composite key, combiing ChainID and Part.
__________________
An optimist sees the glass as half full.
A pessimist sees the glass as half empty.
A realist drinks it no matter how much there is.
SELECT T.*
FROM table AS T,
( SELECT ChainID, MAX(Sequence) AS MaxSequence
FROM table
GROUP BY ChainID ) AS S
WHERE T.ChainID = S.ChainID
AND T.Part = 'Part2'
AND T.Sequence = S.MaxSequence
Hmmm...SQL Server could optimize that, but I'm not so sure about MySQL.
Let me think on it.
__________________
An optimist sees the glass as half full.
A pessimist sees the glass as half empty.
A realist drinks it no matter how much there is.
I think for MySQL using Sequence, if you wanted decent performance, you'd have to do this:
Code:
SELECT T.*
FROM table AS T,
( SELECT T2.ChainID, MAX(T2.Sequence) AS MaxSequence
FROM table AS T1, table AS T2
WHERE T1.ChainID = T2.ChainID
AND T1.Part = 'Part2'
GROUP BY T2.ChainID ) AS S
WHERE T.ChainID = S.ChainID
AND T.Sequence = S.MaxSequence
Which looks more complex and looks like it would be slower, but it won't be.
See, the inner SELECT would immediately find that it only need (in our example) 4 records (the ones with ChainID = 001) and so then it just has to select the MAX from those 4. And then the join to the main T table is lightning fast, because it has already fetch that record as part of the inner SELECT!
__________________
An optimist sees the glass as half full.
A pessimist sees the glass as half empty.
A realist drinks it no matter how much there is.
Do you think there is anyway building in a way of tracking the order of the parts?
LOL! Beat you to it!!
Incidentally, if you used an auto_increment ID for the entire table *AND* the parts were entered in sequence order, then yes, you could use that in place of the Sequence field.
__________________
An optimist sees the glass as half full.
A pessimist sees the glass as half empty.
A realist drinks it no matter how much there is.
Thanks for the Help! autoID would be a good way i think
i'l have a play with getting the data into the correct format and a way of keeping it updated / amended
Sequence wouldn't be needed immediately, but i like to plan ahead to in the future it might well be needed, the AutoID would future proof this as well as keeping the "latest" easier, infact makes the maintenance easier as well as anything added would automacicaly be the latest... as long as the starting data was OK!
Actually, adding another record with same ChainID but a one-greater sequence number isn't that hard.
Example:
Code:
INSERT INTO table ( ChainID, Part, Sequence )
SELECT ChainID, 'new part name', MAX(Sequence) + 1
FROM table
WHERE ChainID = $chosenchainid
GROUP BY ChainID
Or even
Code:
INSERT INTO table ( ChainID, Part, Sequence )
SELECT ChainID, 'new part name', Sequence + 1
FROM table
WHERE ChainID = $chosenchainid
ORDER BY Sequence DESC LIMIT 1
__________________
An optimist sees the glass as half full.
A pessimist sees the glass as half empty.
A realist drinks it no matter how much there is.
Before you post, read our: 
autoID would be a good way i think 
