Re: Coding An SQL Statement

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

> What I would like to do is create a SQL statement that will
> retrieve all of the latest versions for each index number.

Assuming that sop_versionfix is the highest version portion,

SELECT s.sop_index, s.sop_versionfix, s.sop_versionmajor,
MAX(sop_versionminor) AS sop_versionminor
FROM sop s
JOIN
(SELECT s.sop_index, s.sop_versionfix, MAX(sop_versionmajor) AS sop_versionmajor
FROM sop s
JOIN
(SELECT sop_index, MAX(sop_versionfix) AS sop_versionfix FROM sop GROUP BY 1) AS x
USING (sop_index, sop_versionfix)
GROUP BY 1,2) AS y
USING (sop_index, sop_versionfix, sop_versionmajor)
GROUP BY 1,2,3
ORDER BY 1;

- --
Greg Sabino Mullane greg(at)turnstep(dot)com
PGP Key: 0x14964AC8 200507101857
http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8
-----BEGIN PGP SIGNATURE-----

iD8DBQFC0ag+vJuQZxSWSsgRAg01AJwNVuaNWJcEgoN0nUKQgh1lfT/PLQCfUzmB
JcdGO+Qh8sJ4ZR1kd7ba5UM=
=bpve
-----END PGP SIGNATURE-----