| Author |
 Topic  |
|
|
AskSQLTeam
Ask SQLTeam Question
USA
0 Posts |
|
|
sherrys
Starting Member
USA
37 Posts |
 Posted - 06/13/2001 : 13:15:06
|
Thanks, that works great.
|
 |
|
|
Tony Bater
Starting Member
United Kingdom
1 Posts |
Posted - 06/14/2001 : 04:43:34
|
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
|
|
|
|
rythm123us
Starting Member
27 Posts |
Posted - 03/15/2006 : 10:43:00
|
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 |
|
|
|
spirit1
Cybernetic Yak Master
Slovenia
11741 Posts |
Posted - 03/15/2006 : 11:52:59
|
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  |
|
|
|
jhermiz
Flowing Fount of Yak Knowledge
USA
3564 Posts |
Posted - 03/16/2006 : 18:32:30
|
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 |
|
|
|
Player_One
Starting Member
Canada
5 Posts |
Posted - 01/19/2007 : 09:43:44
|
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 |
|
|
| |
Topic |
|
Article: Selecting Based on a Version Field
