>> I have a table that looks like: <<
Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, datatypes, etc. in
your schema are. Sample data is also a good ideas, along with clear
specifications. Is this what you meant, if you had followed the
newsgroup's netiquette?
CREATE TABLE Foobar
(product_id INTEGER NOT NULL,
major_version_nbr INTEGER NOT NULL,
minor_version_nbr INTEGER NOT NULL,
content NTEXT NOT NULL,
PRIMARY KEY (product_id, major_version_nbr, minor_version_nbr));
You also need to read ISO-11179 so that you will stop using vague,
meaningless data element names like "id" (of what??). Next, one of
the rules of data modeling is that you do not split an attribute over
multiple columns; a column is an attribute drawn from one and only one
domain and it represents a complete measurment or value in itself.
That is why you use a date and not three separate columns for year,
month and day.
Likewise, a row in a table is a complete fact, but that is another
topic. Let's fix your mess:
CREATE TABLE Foobar -- done right
(product_id INTEGER NOT NULL,
version_nbr DECIMAL (8,4) NOT NULL,
content NTEXT NOT NULL,
PRIMARY KEY (product_id, version_nbr));
All I want to do is query for ID and Content for the highest
versions of each ID. <<
SELECT F1.product_id, F1.content
FROM Foobar AS F1
WHERE version_nbr
= (SELECT MAX(version_nbr)
FROM Foobar AS F2
WHERE F1.product_id = F2.product_id);
Good rule of thumb: complex queries for simple things are most often
the result of poor schema design. Here is the same thing for your
schema.
SELECT F1.product_id, F1.content
FROM Foobar AS F1
WHERE F1.major_version_nbr
= (SELECT MAX(F2.major_version_nbr)
FROM Foobar AS F2
WHERE F1.product_id = F2.product_id)
AND F1.minor_version_nbr
= (SELECT MAX(F3.minor_version_nbr)
FROM Foobar AS F3
WHERE F1.product_id = F3.product_id
AND F3.major_version_nbr
= (SELECT MAX(F4.major_version_nbr)
FROM Foobar AS F4
WHERE F1.product_id = F2.product_id));