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