Results 1 to 7 of 7
  1. #1
    Join Date
    Mar 2007
    Posts
    2

    Question Unanswered: 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?

    Code:
    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:

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

    Thanks!
    MrsM

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Use a subquery:
    Code:
    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?
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  3. #3
    Join Date
    Mar 2007
    Posts
    2
    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

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by mrsmiggins
    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

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    May 2004
    Location
    Seattle
    Posts
    1,313
    Quote Originally Posted by blindman
    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:

    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"

  6. #6
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Quote Originally Posted by mrsmiggins
    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.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  7. #7
    Join Date
    May 2004
    Location
    Seattle
    Posts
    1,313
    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.
    Last edited by jezemine; 03-13-07 at 03:35.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •