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

Last() Function?

Greetings,
I'm having some trouble to create a report. In this report I'm supposed to get the last row for each group of customers, ordered by date. Let me explain you a bit further using an example:
The table contains these fields, amongst others: CustomerID, Car, LastVisit.
So, for each customer there are many rows, each one with a different value in LastVisit. What I need to get, and before I started it looked quite easy, is a recordset with the last visit of each customer, given a specific car. For example, the last visit of each customer that has a Ford.
Now, I think there's a function LAST() in Access that actually gets the last row in a group by. That would be great, because the only thing I would have to do is grouping by CustomerID and getting the said last row, but unfortunatelly looks like SQL Server 2000 doesn't support that function.
The question is, how can I get that last row in each group? I've already tried using cursors in a stored procedure and temporary tables but I'm afraid I need a less time consuming solution as I'm dealing with more than 1.5 million rows.
If that's not possible, I guess I could "mark" the rows that I need and filter it later, but I'm not sure how to do it either...
If anyone around can give me some advice I would really appreciate it. I apologize if I was not very clear. I'll try to clarify it if you have doubts.
Thanks in advance,
Ricardo Lopez.

Two thoughts:

1. Try using MIN and MAX (whichever gives the correct value in this case) it will work on values which are non-numeric aswell.

2. Otherwise, use top with an order by, and use desc or asc as appropriate, and write a series of sql statments to get each group, and then combine using the UNION ALL operator.

|||

This will get you the results. It is using a derived table to get the last visit date for a customer id and then joining it with the same table to get the car of that visit.

select v.CustomerID, v.Car
from Visits v
inner join (select CustomerID as CustomerID, max(LastVisit) as LastVisit
from Visits
group by CustomerID) vlast
on (vlast.CustomerID = v.CustomerID and vlast.LastVisit = v.LastVisit)


I have assumed that LastVisit is a datetime column.