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,
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.
Description: application/ms-tnef (2.0 KB)
pgsql-novice by date
|Next:||From: Verena Ruff||Date: 2005-07-09 20:20:32|
|Subject: integer and ''|
|Previous:||From: liam.friel||Date: 2005-07-09 13:46:57|
|Subject: Searching docs (.doc .pdf .xls .txt) stored in a PGSQL database|