Query to return latest record, multiple join fields

Hi
I need to write a query that returns the latest value(s) from a table,
'grouped' by the primary key (multiple fields), and the criteria to
derive the latest record is also based on multiple fields.
I have put together the DDL below as a simplified example, and want to
write a query that returns the following resultset:
company--project--value--
1 1 'fifth value'
1 2 '.2 fifth value'
2 1 '2 fifth value'
(KEY: company + project)
(LATEST RECORD: year + batch + item)
Thanks for any help
Sean
---
CREATE TABLE mytable (company INT, project INT, [year] int, batch int,
item int, value varchar(35))
INSERT INTO mytable VALUES (1, 1, 2003, 1, 1, 'first value')
INSERT INTO mytable VALUES (1, 1, 2003, 1, 2, 'second value')
INSERT INTO mytable VALUES (1, 1, 2003, 1, 3, 'third value')
INSERT INTO mytable VALUES (1, 1, 2003, 2, 1, 'fourth value')
INSERT INTO mytable VALUES (1, 1, 2003, 2, 2, 'fifth value')
INSERT INTO mytable VALUES (1, 1, 2002, 1, 1, 'sixth value')
INSERT INTO mytable VALUES (1, 1, 2002, 1, 2, 'seventh value')
INSERT INTO mytable VALUES (1, 1, 2002, 2, 1, 'eighth value')
INSERT INTO mytable VALUES (1, 2, 2003, 1, 1, '.2 first value')
INSERT INTO mytable VALUES (1, 2, 2003, 1, 2, '.2 second value')
INSERT INTO mytable VALUES (1, 2, 2003, 1, 3, '.2 third value')
INSERT INTO mytable VALUES (1, 2, 2003, 2, 1, '.2 fourth value')
INSERT INTO mytable VALUES (1, 2, 2003, 2, 2, '.2 fifth value')
INSERT INTO mytable VALUES (1, 2, 2002, 1, 1, '.2 sixth value')
INSERT INTO mytable VALUES (1, 2, 2002, 1, 2, '.2 seventh value')
INSERT INTO mytable VALUES (1, 2, 2002, 2, 1, '.2 eighth value')
INSERT INTO mytable VALUES (2, 1, 2003, 1, 1, '2 first value')
INSERT INTO mytable VALUES (2, 1, 2003, 1, 2, '2 second value')
INSERT INTO mytable VALUES (2, 1, 2003, 1, 3, '2 third value')
INSERT INTO mytable VALUES (2, 1, 2003, 2, 1, '2 fourth value')
INSERT INTO mytable VALUES (2, 1, 2003, 2, 2, '2 fifth value')
INSERT INTO mytable VALUES (2, 1, 2002, 1, 1, '2 sixth value')
INSERT INTO mytable VALUES (2, 1, 2002, 1, 2, '2 seventh value')
INSERT INTO mytable VALUES (2, 1, 2002, 2, 1, '2 eighth value')
---This table doesn't appear to have a primary key. I'll assume that the key is
supposed to be (company,project,year,batch,item). I've also assumed that the
batch and item numbers are in the range 0-999. If not, you'll have to amend
the YBI calculation accordingly.
SELECT T.company, T.project, T.value
FROM MyTable AS T
JOIN
(SELECT company, project,
MAX([year]*1000000+batch*1000+item) AS ybi
FROM Mytable
GROUP BY company, project) AS M
ON T.company = M.company
AND T.project = M.project
AND T.[year]*1000000+T.batch*1000+T.item = M.ybi
--
David Portas
--
Please reply only to the newsgroup
--sql