Thread: Coding An SQL Statement

I can do basic SQL coding but I am having a hard time figuring this one out.

I have a table with the following fields.

CREATE TABLE sop (
  sop_id bigint(12) NOT NULL auto_increment,
  certcode_code varchar(5) NOT NULL default '',
  sop_index int(5) NOT NULL default '0',
  sop_versionmajor int(3) NOT NULL default '0',
  sop_versionminor int(3) NOT NULL default '0',
  sop_versionfix int(2) NOT NULL default '0',
  sop_date date NOT NULL default '0000-00-00',
  sop_title varchar(35) NOT NULL default '',
  sop_text longtext NOT NULL,
  PRIMARY KEY  (sop_id)
)

(It is coded for MySQL but I am in the process of converting to Postgres.
Wish they were more compatible!)

The index is a number that acts like a catalog number in a library. There
can be multiple occurrences of records with the same index number. The
version information will determine which is the latest. The date is
incidental to the other sequencing fields.

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

SELECT * FROM sop ORDER BY sop_versionmajor, sop_versionminor,
sop_versionfix DESC

That would bring the latest/newest versions to the top. But I want it in
index order ASC. If I add sop_index to the ORDER BY I will have it in
reverse order for printing. Or if I add ASC then I have to find the last
version of each index.

Any ideas, suggestions, coding is GREATLY appreciated.

Thanks in advance.

Mike

-----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=2529DF6AB8F79407E94445B4BC9B906714964AC8
-----BEGIN PGP SIGNATURE-----

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

i installed pgsql 8.03 (not as a service).  the
install procedure went fine, as far as i know, but i
honestly don't know what to do next.  i tried to open
some databases (start->pgsql->checked ona couple
options, can't recall names right now), had a command
line window (i think) flash before me and then...
nothing.  all i saw was my windows background.

should i uninstall (hopefully this is simple) and
reinstall as a service?  i have pgsql running on
cygwin, however, i'd like to get another computer up
and running with 8.03.

tia...



__________________________________
Yahoo! Mail for Mobile
Take Yahoo! Mail with you! Check email on your mobile phone.
http://mobile.yahoo.com/learn/mail