| Greg Sabino Mullane 2005-07-10, 8:23 pm |
|
-----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@turnstep.com
PGP Key: 0x14964AC8 200507101857
http://biglumber.com/x/web? pk=2529...14964
AC8
-----BEGIN PGP SIGNATURE-----
iD8DBQFC0ag+vJuQZxSW
SsgRAg01AJwNVuaNWJcE
goN0nUKQgh1lfT/PLQCfUzmB
JcdGO+Qh8sJ4ZR1kd7ba
5UM=
=bpve
-----END PGP SIGNATURE-----
---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster
|