473,326 Members | 2,128 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,326 software developers and data experts.

Reasonably simple query question

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
10 2226
"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
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
>> 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
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
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
>> [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

"--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
>> 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
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
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

5
by: Maciej Nadolski | last post by:
Hi! I`ve got a simple question but I`m puzzled:( When I create variable: for example $query for query to MySQL its obvieus that I want to use variables. Now should I do something like that: 1)...
3
by: Patchwork | last post by:
Hi Everyone, Please take a look at the following (simple and fun) program: //////////////////////////////////////////////////////////////////////////// ///////////// // Monster Munch, example...
6
by: Eddie Smit | last post by:
field- field- surname town ---- ---- john NY john Vegas john Boston eddie Boston eddie New Orleans eddie NY
15
by: Richard Hollenbeck | last post by:
For example, one college course has only 24 students in it, but the following code says there are zero As, 20 Bs, 16 Cs, 4 Ds, and 8 Fs. When it prints it then says 0 As, 40 Bs, 32 Cs, 8 Ds, and...
13
by: Saber | last post by:
I did a lot of searches and read something about datagrids. But I couldn't find the answer of my simple question, how can I show only my desired columns of a table? for example I wrote this sql...
2
by: Fendi Baba | last post by:
I created a person table with various fields such as Suffix, Salutation, etc, Some of these fields may not be mandatory for example suffix. In the actual table itself, I only have a field for...
3
by: Steven Blair | last post by:
Query application made simple? I have to use ASP.NET quite often to knock up quick protype applications. Generally, these applications have some components for querying and an area of screen...
3
by: psuwebmasters | last post by:
I am doing some work developing a OneBox for a Google Mini. All I need to do is take a $_GET values from the Mini (in this case, specifically "query"), format it into a URI to pass off to another...
9
by: muddasirmunir | last post by:
i have a simple query and does not getting desire results which i want i am using vb6 and access i had a table with with 8 fields but just to simplyfy by question i am just supposing to four. ...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.