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"Chris Vinall" <cvinall@.nospam.myrealbox.com> wrote in message
news:3f7643f5@.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|||Thanks muchly :)

I would never have thought of doing it like that

"John Gilson" <jag@.acm.org> wrote in message
news:eUrdb.53380$u67.35809@.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|||>> 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));|||joe.celko@.northface.edu (--CELKO--) wrote in message news:<a264e7ea.0309281016.7c0f6dcd@.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));|||Joe,

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

Where can this be found?

Thanks in advance,
Christian.|||>> [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!|||"--CELKO--" <joe.celko@.northface.edu> wrote in message
news:a264e7ea.0309281016.7c0f6dcd@.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|||>> 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!|||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!|||Joe Celko (joe.celko@.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, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Really complex query that has me stymied

I'm attempting to write a query that pulls data from two tables in a really
complex way, and I can't figure a way around it. Because of the architecture
in use, it has to be done in a single query, without using a stored
procedure. This is a simplified version of what I'm doing:
Let's say I have two tables, each with four columns, named A, B, C and D:
Table 1
A,B,C,D
1,2,3,5
3,5,7,9
3,9,2,6
3,6,7,1
9,8,3,0
2,5,8,1
Table 2
A,B,C,D
5,3,2,3
4,8,7,1
3,5,7,2
1,4,5,6
8,9,3,2
2,6,2,2
Each of the tables has thousands of records. I need a query that does this:
For each row in table 1, look up the values in table 2. If a row can be
found in table 2 whose columns A, B and C match columns A, B and C in table
1, and whose column D equals 2, then return the row from table 1. Do not
return a row unless all three columns A, B and C match, and Table2.D equals
2.
Using this rule on the table values above, it should be returning only the
row "3,5,7,9" from Table 1 because there is only one row in Table 2 that has
columns A,B,C,D equal to "3,5,7,2"
Here's what I'm trying now:
SELECT * FROM Table1
WHERE
Table1.A IN (SELECT DISTINCT(A) FROM Table2 WHERE Table2.D = 2)
AND
Table1.B IN (SELECT DISTINCT(B) FROM Table2 WHERE Table2.D = 2)
AND
Table1.C IN (SELECT DISTINCT(C) FROM Table2 WHERE Table2.D = 2)
This returns 3,5,7,9, 3,9,2,6 and 3,6,7,1 from Table 1, when it should only
be returning 3,5,7,9. It does this because column A matches (3), Column B
matches multiple records in Table 2 with a Table2.D=2, and so does Column C.
Even though I am specifying AND for the three columns, it is actually acting
as an OR, because each SELECT DISTINCT is separate, and does not take into
consideration the other SELECTs. The first SELECT DISTINCT pulls a result
set, and Column A is compared against it. The second SELECT DISTINCT pulls a
different result set, and Column B is compared against it. And so on. This
results in an additive comparison. What I need, in effect, is for the SELECT
DISTINCT(B) to be more like "Take the SELECT DISTINCT(A) result set and pare
it down further using the SELECT DISTINCT(B) values". Then The SELECT
DISTINCT(C) needs to be more like "Take the SELECT DISTINCT(B) result set
and pare it down even further using the SELECT DISTINCT(C) values".
I hope this makes sense, and that someone can see what I'm missing. I've
spent hours trying to figure out a way of doing this, without success.Scott,
Try this:
SELECT A, B, C, D
FROM [Table 1] AS T1
WHERE EXISTS (
SELECT * FROM [Table 2] AS T2
WHERE T2.A = T1.A
AND T2.B = T1.B
AND T2.C = T1.C
AND T2.D = 2
)
or
SELECT T1.A, T1.B, T1.C, T1.D
FROM [Table 1] AS T1
JOIN [Table 2] AS T2
ON T2.A = T1.A
AND T2.B = T1.B
AND T2.C = T1.C
WHERE
T2.D = 2
The latter query will return duplicate rows, so it could need
DISTINCT. Use whichever is faster (hopefully at least one
has no typos).
Steve Kass
Drew University
Scott MacLean wrote:

>I'm attempting to write a query that pulls data from two tables in a really
>complex way, and I can't figure a way around it. Because of the architectur
e
>in use, it has to be done in a single query, without using a stored
>procedure. This is a simplified version of what I'm doing:
>Let's say I have two tables, each with four columns, named A, B, C and D:
>Table 1
>A,B,C,D
>1,2,3,5
>3,5,7,9
>3,9,2,6
>3,6,7,1
>9,8,3,0
>2,5,8,1
>Table 2
>A,B,C,D
>5,3,2,3
>4,8,7,1
>3,5,7,2
>1,4,5,6
>8,9,3,2
>2,6,2,2
>Each of the tables has thousands of records. I need a query that does this:
>For each row in table 1, look up the values in table 2. If a row can be
>found in table 2 whose columns A, B and C match columns A, B and C in table
>1, and whose column D equals 2, then return the row from table 1. Do not
>return a row unless all three columns A, B and C match, and Table2.D equals
>2.
>Using this rule on the table values above, it should be returning only the
>row "3,5,7,9" from Table 1 because there is only one row in Table 2 that ha
s
>columns A,B,C,D equal to "3,5,7,2"
>Here's what I'm trying now:
>SELECT * FROM Table1
>WHERE
> Table1.A IN (SELECT DISTINCT(A) FROM Table2 WHERE Table2.D = 2)
>AND
> Table1.B IN (SELECT DISTINCT(B) FROM Table2 WHERE Table2.D = 2)
>AND
> Table1.C IN (SELECT DISTINCT(C) FROM Table2 WHERE Table2.D = 2)
>This returns 3,5,7,9, 3,9,2,6 and 3,6,7,1 from Table 1, when it should only
>be returning 3,5,7,9. It does this because column A matches (3), Column B
>matches multiple records in Table 2 with a Table2.D=2, and so does Column C
.
>Even though I am specifying AND for the three columns, it is actually actin
g
>as an OR, because each SELECT DISTINCT is separate, and does not take into
>consideration the other SELECTs. The first SELECT DISTINCT pulls a result
>set, and Column A is compared against it. The second SELECT DISTINCT pulls
a
>different result set, and Column B is compared against it. And so on. This
>results in an additive comparison. What I need, in effect, is for the SELEC
T
>DISTINCT(B) to be more like "Take the SELECT DISTINCT(A) result set and par
e
>it down further using the SELECT DISTINCT(B) values". Then The SELECT
>DISTINCT(C) needs to be more like "Take the SELECT DISTINCT(B) result set
>and pare it down even further using the SELECT DISTINCT(C) values".
>I hope this makes sense, and that someone can see what I'm missing. I've
>spent hours trying to figure out a way of doing this, without success.
>
>|||Hello, Scott
This should do the job:
SELECT * FROM Table1 T1
WHERE EXISTS (
SELECT * FROM Table2 T2
WHERE T1.A=T2.A AND T1.B=T2.B AND T1.C=T2.C
AND T2.D=2
)
Razvan|||Thanks Steve and Razvan, this did the trick. Once I saw this I thought; "of
COURSE" (hand slap on forehead).
Thanks again.
"Razvan Socol" <rsocol@.gmail.com> wrote in message
news:1110347824.072514.140200@.z14g2000cwz.googlegroups.com...
> Hello, Scott
> This should do the job:
> SELECT * FROM Table1 T1
> WHERE EXISTS (
> SELECT * FROM Table2 T2
> WHERE T1.A=T2.A AND T1.B=T2.B AND T1.C=T2.C
> AND T2.D=2
> )
> Razvan
>

Real simple liscensing question for sql server 2005.

Hell, I host web pages on a server(s) that I own, located in a datacenter.

I am trying to figure out how to properly liscnese sql server 2005.

I have called microsoft, and the people i talk to dont seem to understand my situation. They keep asking me about the number of employess i have, which is totally irrelevant.

Let me give you a basic example.

Example 1.

My personal web page has a blog on it. The blog data is stored in a SQL database. Its a popular blog, thousands of anonymous people reading my blog every month via my webpage. The only "thing" accessing the SQL databse is an asp.net script i write, which then turns around and presents the data via html over the www. just like every other blog in the universe.

Example 2.

I sell artwork over the internet via my web page. My web page uses a shopping cart system which makes use of an SQL database to keep track of inventory and orders etc. No one ever tries to connect to my database, they just use the shopping cart on my web page which of course connects to the database.

Now according to the MS liscensing documentation it seems that i can buy sql server w/ 5 device CAL's. Since I only have 1 or 2 webservers accessing the database server, that should be no problem right?

So my basic question is this: is a single Device CAL enough to allow one webserver to connect to one sql server and then show dynamic content to thousands of anonymous users (whoever happens to visit my sites?).

Thank you very much for any help you can provide.

viscious:

I have called microsoft, and the people i talk to dont seem to understand my situation.

You really need to try again to work with Microsoft on licensing, as this is a legal issue and they should be the best source of information on the licensing of their product.

READTEXT error

I can't figure out I continually get a msg 7124 error using READTEXT.
The script:
DECLARE @.TextfieldPtr varbinary(16)
DECLARE @.CommentsBytes int
SELECT @.TextfieldPtr = TEXTPTR(TextComments)
FROM Comments
WHERE CommentId = 25
SELECT @.CommentsBytes = DATALENGTH(TextComments)
FROM Comments
WHERE CommentId = 25
/*This returns a value of 17,830*/
SET TEXTSIZE @.CommentsBytes
READTEXT Comments.TextComments @.TextfieldPtr 0 @.CommentsBytes
The result:
**********
17830
Server: Msg 7124, Level 16, State 1, Line 19
The offset and length specified in the READTEXT statement is greater
than the actual data length of 8915.
***********
Why 8915, which is half of the actual size returned by the DATALENGTH()
function?If it is NVARCHAR, use DATALENGTH(column)/2
On 3/12/05 4:31 PM, in article
1110663061.251893.218680@.z14g2000cwz.googlegroups.com, "Rlane"
<rmathuln@.pacbell.net> wrote:

> I can't figure out I continually get a msg 7124 error using READTEXT.
> The script:
> DECLARE @.TextfieldPtr varbinary(16)
> DECLARE @.CommentsBytes int
> SELECT @.TextfieldPtr = TEXTPTR(TextComments)
> FROM Comments
> WHERE CommentId = 25
> SELECT @.CommentsBytes = DATALENGTH(TextComments)
> FROM Comments
> WHERE CommentId = 25
> /*This returns a value of 17,830*/
> SET TEXTSIZE @.CommentsBytes
> READTEXT Comments.TextComments @.TextfieldPtr 0 @.CommentsBytes
> The result:
> **********
> 17830
> Server: Msg 7124, Level 16, State 1, Line 19
> The offset and length specified in the READTEXT statement is greater
> than the actual data length of 8915.
> ***********
> Why 8915, which is half of the actual size returned by the DATALENGTH()
> function?
>