another sql issue

[blom0344]

I have to restrict a join between 2 tables that have a N:M relationship.
The idea is to fetch only the records from the second table with the highest version_number for each id.
The tables are joined over the id field and the relationship is brought back to a N:1 giving proper results.

I have used thse constructions in the past, but I cannot recall the proper syntax.

A hint please (my sql routines are rusty)

Ties Blom

 

[blom0344]

SELECT M.workflow_id, M.version, N.session_id, N.workflow_id, N.run_date
FROM N INNER JOIN M ON N.workflow_id = M.workflow_id
WHERE M.version = (select max(M.version) from M where n.workflow_id = m.workflow_id);

And suddenly the fog cleared

Ties Blom