By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
464,288 Members | 1,095 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 464,288 IT Pros & Developers. It's quick & easy.

Reasonably simple query question

P: n/a
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
Jul 20 '05 #1
Share this Question
Share on Google+
10 Replies

P: n/a
"Chris Vinall" <cv*****@nospam.myrealbox.com> wrote in message
news:3f******@duster.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.MajorVersion > T1.MajorVersion OR
(T2.MajorVersion = T1.MajorVersion AND
T2.MinorVersion > T1.MinorVersion))
WHERE T2.ID IS NULL

Regards,
jag
Jul 20 '05 #2

P: n/a
Thanks muchly :)

I would never have thought of doing it like that

"John Gilson" <ja*@acm.org> wrote in message
news:eU*******************@twister.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.MajorVersion > T1.MajorVersion OR
(T2.MajorVersion = T1.MajorVersion AND
T2.MinorVersion > T1.MinorVersion))
WHERE T2.ID IS NULL

Regards,
jag

Jul 20 '05 #3

P: n/a
>> 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));
Jul 20 '05 #4

P: n/a
jo*******@northface.edu (--CELKO--) wrote in message news:<a2**************************@posting.google. 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_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);


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

Jul 20 '05 #5

P: n/a
Joe,

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


Where can this be found?

Thanks in advance,
Christian.
Jul 20 '05 #6

P: n/a
>> [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!
Jul 20 '05 #7

P: n/a

"--CELKO--" <jo*******@northface.edu> wrote in message
news:a2**************************@posting.google.c om...
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
Jul 20 '05 #8

P: n/a
>> 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!
Jul 20 '05 #9

P: n/a
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!
Jul 20 '05 #10

P: n/a
Joe Celko (jo*******@northface.edu) writes:
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.


Not all business rules in this world is based on ISO standards, Joe.
--
Erland Sommarskog, SQL Server MVP, so****@algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 20 '05 #11

This discussion thread is closed

Replies have been disabled for this discussion.