SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

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

AskSQLTeam
Ask SQLTeam Question

USA
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.

sherrys
Starting Member

USA
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
Sherry,

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.

Tony
Go to Top of Page

rythm123us
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"?

Thanks,
Rythm
Go to Top of Page

spirit1
Cybernetic Yak Master

Slovenia
11749 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

jhermiz
Flowing Fount of Yak Knowledge

USA
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 -- http://www.web-impulse.com

RS Blog -- http://weblogs.sqlteam.com/jhermiz
Go to Top of Page

Player_One
Starting Member

Canada
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.

SELECT *
FROM Revisions as AllRevs
WHERE EXISTS
   (SELECT NewRevs.version
    FROM Revisions as NewRevs
    WHERE AllRevs.version = NewRevs.version
    GROUP BY NewRevs.version
    HAVING MAX(NewRevs.build)=AllRevs.build)
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  
 New 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.08 seconds. Powered By: Snitz Forums 2000