Determine Most Current Record

[mataichi]

I have a table that contains information regarding documents being uploaded. Periodically, new documents will be uploaded to "replace" the previous document. So I have a document name and an owner that will have multiple rows, but with different other attribute fields.

I would like to create a view in SQL 2005 that can dynamically determine which document is the most current (determined by the most recently added record ie MAX(ID)).

Basically, in a view I would like to have a field like....
IF MAX(ID) for this combination of Document Name & Owner, then true else false
and name this field something like IsCurrent.

I'm not sure if this is possible or what the syntax would look like.

Thanks

[tattooedscorpdc]

This should be possible, at a minimum within your code,

I would grab the list of all docs by the owner..

SELECT doc FROM table
WHERE user = user
ORDER BY id DESC

then grab the first one in the list within your code
Hope this helps,

Larry Darrah
MS Architect Evangelist

[b4web]

For the creation of your view, couldn't you use this select?

select max(id), owner, document from doctable group by owner, document;

Then I think you would have the id of the most current document and use that to get the document...