Skip site navigation (1) Skip section navigation (2)

Re: Coding An SQL Statement

From: "Greg Sabino Mullane" <greg(at)turnstep(dot)com>
To: pgsql-novice(at)postgresql(dot)org
Cc: mavila(at)mich(dot)com
Subject: Re: Coding An SQL Statement
Date: 2005-07-10 23:01:09
Message-ID: 95d30b85fa380302607682bc1147a2b2@biglumber.com (view raw or flat)
Thread:
Lists: pgsql-novice
-----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-----



In response to

Responses

pgsql-novice by date

Next:From: operationsengineer1Date: 2005-07-11 19:22:41
Subject: Install PGSQL 8.03 on WinXP (not service)
Previous:From: Tom LaneDate: 2005-07-10 21:21:41
Subject: Re: invalid input syntax for integer

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group