Reasonably simple query question

[Chris Vinall]

I'm an SQL beginner and this is driving me nuts as it seems simple enough
but I can't figure it out.

I have a table that looks like:

ID: int
MajorVersion: int
MinorVersion: int
Content: ntext

The ID is not the table key - different rows can have the same ID.

The MajorVersion and MinorVersion columns together make up a version number.
Say if a row represented version 1.8 of that ID, MajorVersion would be 1 and
MinorVersion 8.

All I want to do is query for ID and Content for the highest versions of
each ID. I tried using GROUP BY but I can't do that because I can't include
Content in the SELECT then.
Am I going to have to just query for the ID and then do a join to get the
Content?

Thanks for any help

Chris

[John Gilson]

"Chris Vinall" <cv*****@nospam .myrealbox.com> wrote in message
news:3f******@d uster.adelaide. on.net...

I'm an SQL beginner and this is driving me nuts as it seems simple enough
but I can't figure it out.

I have a table that looks like:

ID: int
MajorVersion: int
MinorVersion: int
Content: ntext

The ID is not the table key - different rows can have the same ID.

The MajorVersion and MinorVersion columns together make up a version number.
Say if a row represented version 1.8 of that ID, MajorVersion would be 1 and
MinorVersion 8.

All I want to do is query for ID and Content for the highest versions of
each ID. I tried using GROUP BY but I can't do that because I can't include
Content in the SELECT then.
Am I going to have to just query for the ID and then do a join to get the
Content?

Thanks for any help

Chris

Assume table is T.

SELECT T1.ID, T1.Content
FROM T AS T1
LEFT OUTER JOIN
T AS T2
ON T1.ID = T2.ID AND
(T2.MajorVersio n > T1.MajorVersion OR
(T2.MajorVersio n = T1.MajorVersion AND
T2.MinorVersion > T1.MinorVersion ))
WHERE T2.ID IS NULL

Regards,
jag

[Chris Vinall]

Thanks muchly :)

I would never have thought of doing it like that

"John Gilson" <ja*@acm.org> wrote in message
news:eU******** ***********@twi ster.nyc.rr.com ...

Assume table is T.

SELECT T1.ID, T1.Content
FROM T AS T1
LEFT OUTER JOIN
T AS T2
ON T1.ID = T2.ID AND
(T2.MajorVersio n > T1.MajorVersion OR
(T2.MajorVersio n = T1.MajorVersion AND
T2.MinorVersion > T1.MinorVersion ))
WHERE T2.ID IS NULL

Regards,
jag

[--CELKO--]

>> 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_n br INTEGER NOT NULL,
minor_version_n br INTEGER NOT NULL,
content NTEXT NOT NULL,
PRIMARY KEY (product_id, major_version_n br, minor_version_n br));

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_versio n_nbr
= (SELECT MAX(F2.major_ve rsion_nbr)
FROM Foobar AS F2
WHERE F1.product_id = F2.product_id)
AND F1.minor_versio n_nbr
= (SELECT MAX(F3.minor_ve rsion_nbr)
FROM Foobar AS F3
WHERE F1.product_id = F3.product_id
AND F3.major_versio n_nbr
= (SELECT MAX(F4.major_ve rsion_nbr)
FROM Foobar AS F4
WHERE F1.product_id = F2.product_id)) ;

[Steve Kass]

jo*******@north face.edu (--CELKO--) wrote in message news:<a2******* *************** ****@posting.go ogle.com>...

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_n br INTEGER NOT NULL,
minor_version_n br INTEGER NOT NULL,
content NTEXT NOT NULL,
PRIMARY KEY (product_id, major_version_n br, minor_version_n br));

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

and get the wrong result. Version specifications
are not numbers, so don't store them in a numeric
column. Major version 1, minor version 9 precedes
major version 1, minor version 11, but 1.9 does not
precede 1.11.

By the time version 1.10 rolls around and this query
breaks, it will be a lot of trouble to fix.
SK


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_versio n_nbr
= (SELECT MAX(F2.major_ve rsion_nbr)
FROM Foobar AS F2
WHERE F1.product_id = F2.product_id)
AND F1.minor_versio n_nbr
= (SELECT MAX(F3.minor_ve rsion_nbr)
FROM Foobar AS F3
WHERE F1.product_id = F3.product_id
AND F3.major_versio n_nbr
= (SELECT MAX(F4.major_ve rsion_nbr)
FROM Foobar AS F4
WHERE F1.product_id = F2.product_id)) ;

[Christian Maslen]

Joe,

You also need to read ISO-11179 so that you will stop using vague...

Where can this be found?

Thanks in advance,
Christian.

[Joe Celko]

>> [ISO-11179] Where can this be found? <<

This is an international standard, not a trade secret; Google it. If you
do, you'll a few hundred hits, zipped files, powerpoints, etc.

--CELKO--
=============== ============
Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, datatypes, etc. in your
schema are.

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[Manning]

"--CELKO--" <jo*******@nort hface.edu> wrote in message
news:a2******** *************** ***@posting.goo gle.com...

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.

I build Cognos cubes for a living. Recently I was presented with a database
where they had managed to split a date into not three, but *FOUR* columns. A
2 digit year, a char(3) month (Jan, Feb, etc), an integer week and an
integer 'day of week'. I assumed I was looking at some complicated lookup
table so I asked where the actual dates were stored, and I was told "you're
looking at 'em".

ISO-11179 condensed into one word: "THINK!"

Regards Manning

[Joe Celko]

>> Version specifications are not numbers, so don't store them in a
numeric column. Major version 1, minor version 9 precedes major version
1, minor version 11, but 1.9 does not precede 1.11. <<

Which ISO standard is that? If you want to use lexical sorting, then
display the minor numbers with leading zeroes in the front end.

I like to use a "decimal outline" format when I write documents, which
*is* the ISO and ANSI convention -- look at the major sections of the
SQL-92 standard.

--CELKO--
=============== ============
Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, datatypes, etc. in your
schema are.

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[Joe Celko]

I was presented with a database where they had managed to split a date
into not three, but *FOUR* columns ... assumed I was looking at some
complicated lookup table so I asked where the actual dates were stored,
and I was told "you're looking at 'em". <<

I know it's not funny, but I gotta laugh. The one you see all over the
place is a series of identical tables for months or years that are
constantly being unioned back into a whole. Then someone who has
permissions on only the most recent memvber of the collection decides to
modifiy it ..

--CELKO--
=============== ============
Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, datatypes, etc. in your
schema are.

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!