Please start any new threads on our new site at We've got lots of great SQL Server experts to answer whatever question you can come up with.

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | Active Topics | Members | Search | Forum FAQ
Save Password
Forgot your Password?

 All Forums
 Site Related Forums
 Article Discussion
 Article: Selecting Based on a Version Field
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Ask SQLTeam Question

0 Posts

Posted - 06/10/2001 :  12:15:11  Show Profile  Visit AskSQLTeam's Homepage  Reply with Quote
Sherry writes "I have a table with form_id, lta_id, type, version and other stuff. My users are allowed to fill out a form multiple times. I need to keep the old versions, but for computations, I only want to add up the newest version. The rest of Sherry's question is in the body of the article.

Article Link.

Starting Member

37 Posts

Posted - 06/13/2001 :  13:15:06  Show Profile  Reply with Quote
Thanks, that works great.

Go to Top of Page

Tony Bater
Starting Member

United Kingdom
1 Posts

Posted - 06/14/2001 :  04:43:34  Show Profile  Reply with Quote

I have an alternative strastegy that I use to good effect.
The main system I work with is a patient-centred clinical information system where a full audit trail of all changes to data is essential. I therefore have two parallel databases, a 'live' and an 'archive'. Both have identical tables (except look-ups, only in the 'live' one), but in the 'live' db, the table primary keys are based on the id only, wheras in the 'archive' db the primary keys are id plus version. An update and insert trigger on the live table posts a copy of the updated row into the equivalent archive table. In this way, the live table only contains the most recent version, which is displayed in electronic medical records, wheras the archive table contains all versions. This greatly simplifies and speeds up data retreival, as no complex derived-table joins are needed. As the archive table has no indexes except the PK, the need to maintain all previous rows does not create size problems (the archive tables frequently occupy less space than the live, which are heavily indexed to support data retrieval).
As all the tables contain fields containing the id of the user who posted the change and the date/time the updated row was written, it is simple to create an audit trail procedure which creates a complete change history for any field in any table.

Go to Top of Page

Starting Member

27 Posts

Posted - 03/15/2006 :  10:43:00  Show Profile  Reply with Quote
I have looked at the post and have a simple question. Is it better to use the "WHERE" clause or use an "INNER JOIN"?

Go to Top of Page

Cybernetic Yak Master

11752 Posts

Posted - 03/15/2006 :  11:52:59  Show Profile  Visit spirit1's Homepage  Reply with Quote
use inner join to join table.
use where to filter data from the joined tables.

Go with the flow & have fun! Else fight the flow
Go to Top of Page

Flowing Fount of Yak Knowledge

3564 Posts

Posted - 03/16/2006 :  18:32:30  Show Profile  Visit jhermiz's Homepage  Reply with Quote
I think he / she met performance wise would it be better to do a select with an inner select and use a WHERE statement to join the two tables or whether joining the tables is better. I would say that using the join would be more readable and would be a better performing query.

Keeping the web experience alive --

RS Blog --
Go to Top of Page

Starting Member

5 Posts

Posted - 01/19/2007 :  09:43:44  Show Profile  Reply with Quote
I have a solution that returns all rows of the original table. Makes it more flexible if you have additional columns that may be added or removed. I did this in a view to return just the newest revisions.

To apply this to Sherry's example, change my version to her lta_id and my build to her version.

FROM Revisions as AllRevs
   (SELECT NewRevs.version
    FROM Revisions as NewRevs
    WHERE AllRevs.version = NewRevs.version
    GROUP BY NewRevs.version
ORDER BY AllRevs.version

Edited by - Player_One on 01/19/2007 12:12:50
Go to Top of Page
  Previous Topic Topic Next Topic  
 Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.04 seconds. Powered By: Snitz Forums 2000