latest enabled version of every document (was "help with sql please!")

Hi, I'm having trouble with some SQL. I have a document table which links to a document version table. I'd like to write a query to get the latest enabled version of every document - a resultset with one row per document. Is this possible with resorting to a cursor?

CREATE TABLE tblDoc (
DocId int
)
GO

CREATE TABLE tblDocVersion (
DocVersionId int,
DocId int,
DateCreated datetime,
IsEnabled bit
)
GO

INSERT INTO tblDoc (DocId) VALUES (1)
INSERT INTO tblDoc (DocId) VALUES (2)

INSERT INTO tblDocVersion (DocVersionId, DocId, DateCreated, IsEnabled) VALUES (1, 1, '8/8/2006' , 1)
INSERT INTO tblDocVersion (DocVersionId, DocId, DateCreated, IsEnabled) VALUES (1, 1, '9/9/2006' , 1)
INSERT INTO tblDocVersion (DocVersionId, DocId, DateCreated, IsEnabled) VALUES (1, 1, '10/10/2006' , 0)
INSERT INTO tblDocVersion (DocVersionId, DocId, DateCreated, IsEnabled) VALUES (1, 2, '8/8/2006' , 1)
INSERT INTO tblDocVersion (DocVersionId, DocId, DateCreated, IsEnabled) VALUES (1, 2, '11/11/2006' , 1)

Desired output:

DocId DocVersionId DateCreated
============================
1 2 9/9/2006
2 5 11/11/2006

Thanks!
MrsMUse a subquery:

select tblDocVersion.DocId,
tblDoVersion.DocVersionID,
tblDocVersion.DateCreated
from tblDocVersion
inner join --LastVersions
(select DocId,
max(DateCreated) as DateCreated
from tblDocVersion
group by DocId) LastVersion
on tblDocVersion.DocId = LastVersion.DocID
and tblDocVersion.DateCreated = LastVersion.DateCreated

By the way...why do you prefix your table name with "tbl"? Are you afraid you are going to forget what it is? Did your husbandBOB call and remind you to pick up some petfoodALPO for your dogFIDO today?|||ah nice, joining on a sub query - never thought of that, thanks a lot.

the tbl thing is part of the standards at my company. doesn't do anyone any harm!

thanks again|||the tbl thing is part of the standards at my company. doesn't do anyone any harm!that's highly doubtful

not the "part of the standards at my company" part

the other part

:)|||Did your husbandBOB call and remind you to pick up some petfoodALPO for your dogFIDO today?

hah! :)

i always hated hungarian. intellisense seems to be slowly killing it off in the world of compiled code thankfully. from the bible of umaintainable code (http://mindprod.com/jgloss/unmain.html):

a_crszkvc30LastNameCol:

"It took a team of maintenance engineers nearly 3 days to figure out that this whopper variable name described a const, reference, function argument that was holding information from a database column of type Varchar[30] named "LastName" which was part of the table's primary key"|||the tbl thing is part of the standards at my company. doesn't do anyone any harm!Why don't you suggest a standard that all desktop PCs be painted schoolbus yellow, all laptops be candy-striped red, and all printers have blue polka-dots? Also harmless, and just as sensible.|||I knew one team at MS that had a convention of putting _sp at the end of every sproc.

Presumably this was because they knew there was some downside to putting sp_ at the front.

I'm sure it really helped them distingush procs from tables, which, btw, all had _tbl at the end. ;)

Large sql statement with 5000 insert and update commands

Hi
What can I do to speed up the processing of a large amount of insert
and update commands that are exchanged using a notepad document.
The document has, say 5000, individual sql statements that either
update or insert.
Is there something in the database that can be turned on or off to
speed up this processing.
thanks
NewishBy default, each INSERT is an implicit transaction and thus requires a
synchronous log i/o for each statement. If you specify an explicit BEGIN
TRANSACTION at the beginning of the script and a COMMIT at the end, you will
get improved performance because log i/o is reduced and only the log flush
for the COMMIT is synchronous.
Hope this helps.
Dan Guzman
SQL Server MVP
"Newish" <ahussain3@.gmail.com> wrote in message
news:1160325892.791516.228990@.i3g2000cwc.googlegroups.com...
> Hi
> What can I do to speed up the processing of a large amount of insert
> and update commands that are exchanged using a notepad document.
> The document has, say 5000, individual sql statements that either
> update or insert.
> Is there something in the database that can be turned on or off to
> speed up this processing.
> thanks
> Newish
>

Large sql statement with 5000 insert and update commands

Hi
What can I do to speed up the processing of a large amount of insert
and update commands that are exchanged using a notepad document.
The document has, say 5000, individual sql statements that either
update or insert.
Is there something in the database that can be turned on or off to
speed up this processing.
thanks
Newish
Hello Newish,
If you batch the inserts in a transaction you should get an improvement.
around each 2000 statements do a BEGIN TRANSACTION and a COMMIT TRANSACTION.
Simon Sabin
SQL Server MVP
http://sqlblogcasts.com/blogs/simons

> Hi
> What can I do to speed up the processing of a large amount of insert
> and update commands that are exchanged using a notepad document.
> The document has, say 5000, individual sql statements that either
> update or insert.
> Is there something in the database that can be turned on or off to
> speed up this processing.
> thanks
> Newish
>

Large sql statement with 5000 insert and update commands

Hi
What can I do to speed up the processing of a large amount of insert
and update commands that are exchanged using a notepad document.
The document has, say 5000, individual sql statements that either
update or insert.
Is there something in the database that can be turned on or off to
speed up this processing.
thanks
Newish
By default, each INSERT is an implicit transaction and thus requires a
synchronous log i/o for each statement. If you specify an explicit BEGIN
TRANSACTION at the beginning of the script and a COMMIT at the end, you will
get improved performance because log i/o is reduced and only the log flush
for the COMMIT is synchronous.
Hope this helps.
Dan Guzman
SQL Server MVP
"Newish" <ahussain3@.gmail.com> wrote in message
news:1160325892.791516.228990@.i3g2000cwc.googlegro ups.com...
> Hi
> What can I do to speed up the processing of a large amount of insert
> and update commands that are exchanged using a notepad document.
> The document has, say 5000, individual sql statements that either
> update or insert.
> Is there something in the database that can be turned on or off to
> speed up this processing.
> thanks
> Newish
>

Large sql statement with 5000 insert and update commands

Hi
What can I do to speed up the processing of a large amount of insert
and update commands that are exchanged using a notepad document.
The document has, say 5000, individual sql statements that either
update or insert.
Is there something in the database that can be turned on or off to
speed up this processing.
thanks
NewishHello Newish,
If you batch the inserts in a transaction you should get an improvement.
around each 2000 statements do a BEGIN TRANSACTION and a COMMIT TRANSACTION.
Simon Sabin
SQL Server MVP
http://sqlblogcasts.com/blogs/simons
> Hi
> What can I do to speed up the processing of a large amount of insert
> and update commands that are exchanged using a notepad document.
> The document has, say 5000, individual sql statements that either
> update or insert.
> Is there something in the database that can be turned on or off to
> speed up this processing.
> thanks
> Newish
>

Large sql statement with 5000 insert and update commands

Hi
What can I do to speed up the processing of a large amount of insert
and update commands that are exchanged using a notepad document.
The document has, say 5000, individual sql statements that either
update or insert.
Is there something in the database that can be turned on or off to
speed up this processing.
thanks
NewishBy default, each INSERT is an implicit transaction and thus requires a
synchronous log i/o for each statement. If you specify an explicit BEGIN
TRANSACTION at the beginning of the script and a COMMIT at the end, you will
get improved performance because log i/o is reduced and only the log flush
for the COMMIT is synchronous.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Newish" <ahussain3@.gmail.com> wrote in message
news:1160325892.791516.228990@.i3g2000cwc.googlegroups.com...
> Hi
> What can I do to speed up the processing of a large amount of insert
> and update commands that are exchanged using a notepad document.
> The document has, say 5000, individual sql statements that either
> update or insert.
> Is there something in the database that can be turned on or off to
> speed up this processing.
> thanks
> Newish
>