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

Coding An SQL Statement

From: "Michael Avila" <mavila(at)mich(dot)com>
To: "Pgsql-Novice" <pgsql-novice(at)postgresql(dot)org>
Subject: Coding An SQL Statement
Date: 2005-07-09 19:06:06
Message-ID: NBBBLNPHAMCFENDFHIDCOEBBEDAA.mavila@mich.com (view raw or flat)
Thread:
Lists: pgsql-novice
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


Attachment: winmail.dat
Description: application/ms-tnef (2.0 KB)

Responses

pgsql-novice by date

Next:From: Verena RuffDate: 2005-07-09 20:20:32
Subject: integer and ''
Previous:From: liam.frielDate: 2005-07-09 13:46:57
Subject: Searching docs (.doc .pdf .xls .txt) stored in a PGSQL database

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