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) )

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.