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]

Re: Reasonably simple query question

"Chris Vinall" <cvinall@nospam .myrealbox.com> wrote in message
news:3f7643f5@d uster.adelaide. on.net...[color=blue]
> 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[/color]

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]

Re: Reasonably simple query question

Thanks muchly :)

I would never have thought of doing it like that

"John Gilson" <jag@acm.org> wrote in message
news:eUrdb.5338 0$u67.35809@twi ster.nyc.rr.com ...[color=blue]
>
> 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
>
>[/color]

[--CELKO--]

Re: Reasonably simple query question

>> 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));
[color=blue][color=green]
>> All I want to do is query for ID and Content for the highest[/color][/color]
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]

Re: Reasonably simple query question

joe.celko@north face.edu (--CELKO--) wrote in message news:<a264e7ea. 0309281016.7c0f 6dcd@posting.go ogle.com>...[color=blue][color=green][color=darkred]
> >> I have a table that looks like: <<[/color][/color]
>
> 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));
>[color=green][color=darkred]
> >> All I want to do is query for ID and Content for the highest[/color][/color]
> 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);[/color]

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

[color=blue]
>
> 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)) ;[/color]

[Christian Maslen]

Re: Reasonably simple query question

Joe,
[color=blue]
>
>You also need to read ISO-11179 so that you will stop using vague...
>[/color]

Where can this be found?

Thanks in advance,
Christian.

[Joe Celko]

Re: Reasonably simple query question

>> [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]

Re: Reasonably simple query question


"--CELKO--" <joe.celko@nort hface.edu> wrote in message
news:a264e7ea.0 309281016.7c0f6 dcd@posting.goo gle.com...[color=blue]
> 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.[/color]

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]

Re: Reasonably simple query question

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

Re: Reasonably simple query question

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!

[Erland Sommarskog]

Re: Reasonably simple query question

Joe Celko (joe.celko@nort hface.edu) writes:[color=blue][color=green][color=darkred]
>>> Version specifications are not numbers, so don't store them in a[/color][/color]
> 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.[/color]

Not all business rules in this world is based on ISO standards, Joe.


--
Erland Sommarskog, SQL Server MVP, sommar@algonet. se

Books Online for SQL Server SP3 at

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp